DB2 count distinct on multiple columns

Question

I am trying to find count and distinct of multiple values but its not worikng in db2

select count(distinct col1, col2) from table

it throws syntax error that count has multiple columns. any way to achieve this

column 1 column 2 date
1             a   2022-12-01
1             a   2022-12-01
2              a  2022-11-30
2              b  2022-11-30
1             b   2022-12-01

i want output

column1 column2 date        count
1         a      2022-12-01  2
2         a      2022-11-30  1
2         b      2022-11-30  1
1         a      2022-12-01  1

Answer 1

The following query returns exactly what you want.

WITH MYTAB (column1, column2, date) AS
(
VALUES
  (1, 'a', '2022-12-01')
, (1, 'a', '2022-12-01')
, (2, 'a', '2022-11-30')
, (2, 'b', '2022-11-30')
, (1, 'b', '2022-12-01')
)
SELECT
  column1
, column2
, date
, COUNT (*) AS CNT
FROM MYTAB
GROUP BY 
  column1
, column2
, date

COLUMN1	COLUMN2	DATE	CNT
1	a	2022-12-01	2
1	b	2022-12-01	1
2	a	2022-11-30	1
2	b	2022-11-30	1

fiddle