DB2在多个列上计数不同

Question

我试图找到多个值的计数和不同，但它不工作在db2

select count(distinct col1, col2) from table

抛出语法错误，计数有多个列。任何实现这个的方法

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

下面的查询返回您想要的结果。

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

不确定你在找什么…

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

或
select count(distinct col1 CONCAT col2) from table

我如何解释"多个值的不同计数"?