CODEWITHSUNDEEP
sqlsql-serversql-server-2012ssms
psysky
psysky

Reputation: 3195

frequency tables by groups in sql

Simple query

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

How can i calculate frequency tables by groups

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

To be more clear, as output i want

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

I tried do that

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

but got the error

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

How can i calculate frequency tables by groups?

Upvotes: 0

Views: 93

Answers (1)

Ankur Patel
Ankur Patel

Reputation: 1423

Try the following:

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

Upvotes: 4

Related Questions