Let's say I have this query :

 select count(*), extract(month from my_table_date), extract(year from my_table_date)
 from my_table
 group by extract(year from my_table_date), extract(month from my_table_date)
 order by extract(year from my_table_date), extract(month from my_table_date);

I have a result like this :

       75                       11                    2020
       56                       12                    2020
       91                        1                    2021
       102                        2                    2021

I would like to add all of the count(*) (column 1) values in the same query. is that possible ?

thank you

CodePudding user response：

Use ROLLUP:

SELECT count(*),
       extract(month from my_table_date) AS month,
       extract(year from my_table_date) as year
FROM   my_table
GROUP BY
       ROLLUP(
         extract(year from my_table_date),
         extract(month from my_table_date)
       )
HAVING GROUPING_ID(
         extract(year from my_table_date),
         extract(month from my_table_date)
       ) IN (0,3)
ORDER BY
       year, month;

Which, for the sample data:

CREATE TABLE my_table (my_table_date) AS
SELECT DATE '2020-11-01' FROM DUAL CONNECT BY LEVEL <= 75 UNION ALL
SELECT DATE '2020-12-01' FROM DUAL CONNECT BY LEVEL <= 56 UNION ALL
SELECT DATE '2021-01-01' FROM DUAL CONNECT BY LEVEL <= 91 UNION ALL
SELECT DATE '2021-02-01' FROM DUAL CONNECT BY LEVEL <= 102;

Outputs:

COUNT(*)	MONTH	YEAR
75	11	2020
56	12	2020
91	1	2021
102	2	2021
324

db<>fiddle here