2012年2月9日星期四

basic sql question

hi

I need to add all the records being counted for all groups.

I would like to add this count for each group into a total for all
groups( the 8 rows). Is this possible?

MSG_CD MSG_TEXT
COUNT(CCHDBA.III_SYNC_RESULT.I
-- ----------------
----------
E1349 SID NOT FOUND IN CCH DATA
254
E1350 STATUS FLAG DOES NOT MATCH
19
E1351 FBI NUMBER IN CCH DOESNT EXIST IN FBI SYNC TAPE
47
E1352 FBI NAME NOT FOUND IN CCH
227
E1353 FBI DOB DOESNT MATCH DOB IN CCH
2
E1354 FBI SEX CODE DOESNT MATCH IN CCH
46
E1355 FBI RACE CODE DOESNT MATCH IN CCH
2998
E1379 SID IN CCH NOT IN FBI SYNC - FBI NUMBER DOES EXIST
25

8 rows selected

SQL
SELECT
CCHDBA.III_RESULT_MESSAGE_XREF.MSG_CD,
CCH_MESSAGE.MSG_TEXT,
count(CCHDBA.III_SYNC_RESULT.III_RESULT_ID)
FROM CCHDBA.III_SYNC_RESULT,
CCHDBA.III_RESULT_MESSAGE_XREF,
CCH_MESSAGE
WHERE ( III_SYNC_RESULT.III_RESULT_ID =
CCHDBA.III_RESULT_MESSAGE_XREF.III_RESULT_ID )
AND ( III_RESULT_MESSAGE_XREF.MSG_CD = CCH_MESSAGE.MSG_CD )
GROUP BY CCHDBA.III_RESULT_MESSAGE_XREF.MSG_CD,
CCH_MESSAGE.MSG_TEXT;

Inappropriate post?
If so, Red Flag it!jwa6 (jwagans@.yahoo.com) writes:
> I need to add all the records being counted for all groups.
> I would like to add this count for each group into a total for all
> groups( the 8 rows). Is this possible?

I will have to admit that I don't understand what you are looking
for.

The standard recommendation for this type of question is to include:

o CREATE TABLE statements for your table.
o INSERT statements with sample data (just enough rows to demonstrate
the problem.)
o The desired result given the sample.
o A short narrative of what you are trying to achieve.

This permits people to post a tested solution to your problem.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"jwa6" <jwagans@.yahoo.com> wrote in message
news:1111693328.831625.88890@.f14g2000cwb.googlegro ups.com...
> hi
> I need to add all the records being counted for all groups.
> I would like to add this count for each group into a total for all
> groups( the 8 rows). Is this possible?

If I understand you correctly, you could add WITH ROLLUP to the GROUP BY
clause. Having said that, your post looks rather like Oracle, not MSSQL - if
so, you should probably post in an Oracle group to get a better answer. If
you are using MSSQL, then as Erland said, please post some code to show
exactly what you want.

Simon

没有评论:

发表评论