total resords count on grouping 3

[jwa6]

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;

 

[JohnDTampaBay]

Check out WHERE clause, SELECT in BOL, specifically the ROLLUP portion.

ROLLUP

Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. Groups are summarized in a hierarchical order, from the lowest level in the group to the highest. The group hierarchy is determined by the order in which the grouping columns are specified. Changing the order of the grouping columns can affect the number of rows produced in the result set.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[jwa6]

I dont understand.

do you have an example?

thanks

jima

 

[ESquared]

BOL = SQL Server Books Online.

It's the built-in help files included when you install SQL Server or Enterprise Manager.

You may also use SQL Server Books Online... Online.

 

[gjsaturday]

I've used something similar myself, but don't have the exact code with me.

it should look something like the following...

SELECT case grouping(CCHDBA.III_RESULT_MESSAGE_XREF.MSG_CD) when 0 then CCHDBA.III_RESULT_MESSAGE_XREF.MSG_CD else 'Total' ,
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 with rollup

Hope that works.