SQL 中按组划分的频率表

Question

简单查询

SELECT  [Dt]
,[CustomerName]
,[ItemRelation]
,[DocumentNum]
,[DocumentYear]
,[CustomerType]
FROM [Action].[dbo].[promo_data]

如何按组计算频率表

[CustomerName]+[ItemRelation]+[DocumentNum]+[DocumentYear]+[CustomerType]

更清楚一点，作为我想要的输出

 [CustomerName] [ItemRelation]  [DocumentNum]   [DocumentYear]  [CustomerType]  count
dix  11111              123             2017              FC    23
5ive  2222               333              2018             OPT    123

我试过这样做

select count (distinct [CustomerName]
,[ItemRelation]
,[DocumentNum]
,[DocumentYear]
,[CustomerType]) from [Action].[dbo].[promo_data]

但得到错误

Message 102, level 15, state 1, line 11
Invalid syntax near the construction ",".

如何按组计算频率表？

Answer 1

尝试以下操作：

SELECT [CustomerName]
,[ItemRelation]
,[DocumentNum]
,[DocumentYear]
,[CustomerType]
,Count(*) as cnt
FROM [Action].[dbo].[promo_data]
GROUP BY
[CustomerName]
,[ItemRelation]
,[DocumentNum]
,[DocumentYear]
,[CustomerType]