CODEWITHSUNDEEP
sqloracle-database
BugsOverflow
BugsOverflow

Reputation: 538

How to make a sum which involves a count

I have the following query where I make a COUNT:

SELECT FECHAGENERACION, IDAPLICACION, COUNT(*) as NRO_REGISTROS
FROM  ADMBLOQMON.BOC_MAEBLOQAUX_BLOQ_MONO  WHERE fechageneracion between '01/12/20' AND '02/12/20' AND IDAPLICACION = 01
GROUP BY FECHAGENERACION, IDAPLICACION
ORDER BY 1,2;

Which gives me the following results:

01/12/20    01    62172
02/12/20    01    61812

How can I make this query also give me that sum of the counts 62172 and 61812? I am struggling so hard please help

Upvotes: 1

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

In Oracle, you can use GROUPING SETS:

SELECT FECHAGENERACION, IDAPLICACION, COUNT(*) as NRO_REGISTROS
FROM ADMBLOQMON.BOC_MAEBLOQAUX_BLOQ_MONO 
WHERE fechageneracion between DATE '2020-01-12' AND DATE '2020-02-12' AND
      IDAPLICACION = 01
GROUP BY GROUPING SETS ( (FECHAGENERACION, IDAPLICACION), () )
ORDER BY 1,2;

Note that I fixed the date constants using standard YYYY-MM-DD format (I had to guess what the dates mean so that might be wrong). I strongly recommend using standard formats for dates so local settings do not change the meaning of your code (and the code is more readable).

Upvotes: 1

Related Questions