Summing a count (*) colum

Database
Enthusiast

Summing a count (*) colum

Hi there,
I have been trying to sum the MY_ID_COUNT (i.e. MY_ID, count (*)) column but without success.
Would be cool to have the total displayed.

Curent resultset looks like this:
---------+---------+---------+---------+---------+---------+---------+
MY_ID       MY_ID_COUNT  MY_ID_DATE                  MY_ID_STATUS      
1234567890           33  yyyy-mm-dd-hh.mm.ss.mmssss  DNOETH 
0987654321            4  yyyy-mm-dd-hh.mm.ss.mmssss  RAJAKT 
1239874561            7  yyyy-mm-dd-hh.mm.ss.mmssss  FEINHOLZ 
9874563217           65  yyyy-mm-dd-hh.mm.ss.mmssss  ULRICH

My preference is for the resulset displayed like this:

MY_ID       MY_ID_COUNT  MY_ID_DATE                  MY_ID_STATUS      
1234567890           33  yyyy-mm-dd-hh.mm.ss.mmssss  DNOETH 
0987654321            4  yyyy-mm-dd-hh.mm.ss.mmssss  RAJAKT 
1239874561            7  yyyy-mm-dd-hh.mm.ss.mmssss  FEINHOLZ 
9874563217           65  yyyy-mm-dd-hh.mm.ss.mmssss  ULRICH

Grand total     109 (it doesnt matter where this total is dispayed; bottom or top)

my current sql:

SELECT  MY_ID, COUNT(*) MY_ID_COUNT
, MAX(DATE) MY_ID_DATE
, MIN(DNOETHSTS) MY_ID_STATUS            

FROM

(                                                          
SELECT Col_date AS DATE
,SUBSTR(Col_conc, 2,7)||SUBSTR(Col_conc, 11,3) MY_ID
,Col_sts DNOETHSTS                                           

FROM  DB.TABLE                                      
WHERE Col_date > 'yyyy-mm-dd hh:mm:ss'                        
AND Col_sts IN ('code1...n')

) DNOETHREQ

GROUP BY MY_ID
ORDER BY MY_ID_STATUS
;         
1 REPLY
Junior Supporter

Re: Summing a count (*) colum

Hi.

You could user aggregate clauses like GROUP BY ROLLUP, CUBE, etc... to get total results.

HTH.

Cheers.

Carlos.