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

CodePudding user response：

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

fiddle

CodePudding user response：

Not exactly sure of what you are looking for...

but
select count(distinct col1), count(distinct col2) from table

or
select count(distinct col1 CONCAT col2) from table

Are how I would interpret "distinct count of multiple values" in a table..