Records groupping/counting with zero counts-CodePudding

I have a table with services containing columns ACT_MONTH (activation month) and S_TYPE (service type). I need SQL query that will return a table showing ALL MONTHS and ALL TYPE table combinations with corresponding number of services activated in that month. The problem is when there's no service in some month/type but I need them in this table as zeros. Simple GROUP BY does not show these zero groups.

So the resulting table should look like:

ACT_MONTH | S_TYPE | QTY
========== ======== =====
2022M01   | A      |  20
2022M01   | B      |  33
2022M02   | A      |   6
2022M02   | B      |   0
2022M03   | A      |  12
2022M03   | B      |   4
2022M04   | A      |   0
2022M04   | B      |   0
2022M05   | A      |   0
2022M05   | B      |   0
...
2022M12   | A      |   0
2022M12   | B      |   0

CodePudding user response：

You can create a calendar and then use a PARTITIONed join, partitioning by s_type, to make sure there is a combination for each month and s_type:

WITH calendar (month) AS (
  SELECT TO_CHAR(
           ADD_MONTHS(DATE '2022-01-01', LEVEL - 1),
           'YYYY"M"MM'
         ) AS month
  FROM   DUAL
  CONNECT BY LEVEL <= 12
)
SELECT c.month AS act_month,
       t.s_type,
       COUNT(t.act_month) AS qty
FROM   calendar c
       LEFT OUTER JOIN table_name t
       PARTITION BY (t.s_type)
       ON (c.month = t.act_month)
GROUP BY
       c.month,
       t.s_type
ORDER BY
       c.month,
       t.s_type;

Which, for the sample data:

CREATE TABLE table_name (act_month, s_type) AS
SELECT '2022M01', 'A' FROM DUAL CONNECT BY LEVEL <= 20 UNION ALL
SELECT '2022M01', 'B' FROM DUAL CONNECT BY LEVEL <= 33 UNION ALL
SELECT '2022M02', 'A' FROM DUAL CONNECT BY LEVEL <=  6 UNION ALL
SELECT '2022M03', 'A' FROM DUAL CONNECT BY LEVEL <= 12 UNION ALL
SELECT '2022M03', 'B' FROM DUAL CONNECT BY LEVEL <=  4;

Outputs:

ACT_MONTH S_TYPE QTY

2022M01 A 20

2022M01 B 33

2022M02 A 6

2022M02 B 0

2022M03 A 12

2022M03 B 4

2022M04 A 0

2022M04 B 0

... ... ...

2022M12 A 0

2022M12 B 0

ACT_MONTH	S_TYPE	QTY
2022M01	A	20
2022M01	B	33
2022M02	A	6
2022M02	B	0
2022M03	A	12
2022M03	B	4
2022M04	A	0
2022M04	B	0
...	...	...
2022M12	A	0
2022M12	B	0

db<>fiddle here

CodePudding user response：

In the meantime I found myself some solution:

SELECT
  m,
  s,
  (SELECT COUNT(*) FROM services_table WHERE s_type=s AND act_month=m) qty
FROM
(
  SELECT m,s
  FROM
  (
    SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),LEVEL-1),'YYYY"M"MM') AS m
    FROM DUAL CONNECT BY LEVEL <= 12
  )
  CROSS JOIN 
  (
    SELECT DISTINCT s_type s FROM services_table
  )
)
ORDER BY 1,2