CODEWITHSUNDEEP
sqlsql-servert-sql
shamim
shamim

Reputation: 6768

How to avoid multiple group by in SQL Server

The table contains below type records, want to get the total-client on specific product year

CREATE TABLE [dbo].[contectTable]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ContentDate] [datetime] NULL,
    [ProductId] [int] NULL,
    [ClientId] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[contectTable] 
    ADD CONSTRAINT [DF_contectTable_ContentDate] 
        DEFAULT (getdate()) FOR [ContentDate]
GO

Table content:

Id  ContentDate            ProductId    ClientId
------------------------------------------------
1   2018-06-23 00:24:58.870        1    1
2   2018-06-23 00:25:14.593        1    1
3   2018-06-23 00:25:21.460        2    3
4   2018-06-23 00:25:28.730        3    8
5   2018-06-23 00:25:36.813        4    1
6   2017-06-23 00:32:42.883        4    2
7   2016-06-23 00:32:51.680        4    3
8   2017-06-23 00:32:59.917        4    4

Suppose:

This SQL query returns the desired result, but it needs to group multiple times. Is there any another way to avoid multiple groups?

SELECT
    y.*
FROM 
    (SELECT
         x.ContentDate,
         x.ProductId,
         SUM(x.countClientId) 'TotalClient'
     FROM 
         (SELECT
              YEAR(ContentDate) ContentDate,
              ProductId,
              ClientId,
              COUNT(ClientId) countClientId
          FROM 
              contectTable
          GROUP BY 
              YEAR(ContentDate), ProductId, ClientId
          HAVING 
              COUNT(ClientId) = 1) x
    GROUP BY 
        x.ContentDate, x.ProductId

    UNION

    SELECT
        x.ContentDate,
        x.ProductId,
        SUM(x.countClientId) 'TotalClient'
    FROM 
        (SELECT
             YEAR(ContentDate) ContentDate,
             ProductId,
             ClientId,
             COUNT(ClientId) countClientId
         FROM 
             contectTable
         GROUP BY 
             YEAR(ContentDate), ProductId, ClientId
         HAVING 
             COUNT(ClientId) > 1) x
    GROUP BY 
        x.ContentDate, x.ProductId) y

Query output is:

ContentDate ProductId   TotalClient
-----------------------------------
2016                4   1
2017                4   2
2018                1   2
2018                2   1
2018                3   1
2018                4   1

Upvotes: 0

Views: 301

Answers (1)

sgeddes
sgeddes

Reputation: 62841

If I'm understanding your question correctly, it looks like you just want to count the number of clients for each year for each product. If so, then this should be all you need:

select year(contentdate), productid, count(clientid)
from contectTable
group by year(contentdate), productid

Upvotes: 4

Related Questions