CODEWITHSUNDEEP
sqlsql-server
M O H
M O H

Reputation: 284

How to query rows with distinct column values grouped by date

I have a query that counts (using SUM) separated groups depending on a column value, and then groups them by months. I think a better explanation would be to share the concept.

Consider this table (myTable):

Id       ItemID    Color           CreatedDate
--------------------------------------------------
1          2        Red          someDateTimeStamp
2          3        Blue         someDateTimeStamp
3          4        Green        someDateTimeStamp
4          5        Blue         someDateTimeStamp
5          2        Red          someDateTimeStamp
6          3        Purple       someDateTimeStamp
7          2        Blue         someDateTimeStamp
8          3        Blue         someDateTimeStamp
9          3        Blue         someDateTimeStamp

So Id is the primary key, ItemID is a foreign key. Not that it is not obvious.

My query:

SELECT TOP 12 
    DateAdd(MONTH, DATEDIFF(MONTH, 0, t.CreatedDate), 0) AS [Date],     
    SUM(CASE WHEN t.Color = 'Red' or t.Color = 'Green' THEN 1 ELSE 0 END) AS firstCount,
    SUM(CASE WHEN t.Color = 'Blue' THEN 1 ELSE 0 END) AS secondCount
FROM 
    myTable t
Group By 
    DateAdd(MONTH, DATEDIFF(MONTH, 0, t.CreatedDate), 0)
ORDER BY 
    DateAdd(MONTH, DATEDIFF(MONTH, 0, t.CreatedDate), 0)

This query counts the Colors and groups them by month for the last 12 months. It works fine and counts everything as expected, until I realized that I should not include the same [ItemID] per month. As you can see it can appear more than once at anytime, and I only need to count it once per month.

If the above table values all fall within the same month, my current query returns:

    Date                firstCount         secondCount
------------------------------------------------------
    someDateStamp           3                   5

What it should return:

    Date                firstCount         secondCount
 ------------------------------------------------------
    someDateStamp           2                   3

I first thought that I can use something like: 

HAVING count(t.ItemID) = 1

but that obviously excludes all that are more than 1.

If any further explanation is required please let me know.

Thanks.

Upvotes: 2

Views: 435

Answers (2)

sandeep rawat
sandeep rawat

Reputation: 4957

You can this

1 Get distinct value in cte .

2 create rank()

sample 

    ;with resut as  
    (
    select distinct  ItemID,   
                    Color,
                    DateAdd(MONTH, DATEDIFF(MONTH, 0, t.CreatedDate), 0) AS [Date]
    from myTable t
    )

    SELECT TOP 12 
    [Date],
    SUM(CASE WHEN t.Color = 'Red' or t.Color = 'Green' THEN 1 ELSE 0 END) AS firstCount,
    SUM(CASE WHEN t.Color = 'Blue' THEN 1 ELSE 0 END) AS secondCount

    from resut t
    Group By [Date]
    order by [Date]

Upvotes: 1

kostas
kostas

Reputation: 461

You could tweak the mytable:

...

 SELECT 
CreatedMonth,     
SUM(CASE WHEN t.Color = 'Red' or t.Color = 'Green' THEN 1 ELSE 0 END)
AS firstCount,
SUM(CASE WHEN t.Color = 'Blue' THEN 1 ELSE 0 END) AS secondCount

FROM
  ( select ItemID,Color,
    DateAdd(MONTH, DATEDIFF(MONTH, 0, CreatedDate), 0) as CreatedMonth
    from mytable 
    group by ItemID,Color,
    DateAdd(MONTH, DATEDIFF(MONTH, 0, CreatedDate), 0)) t

Group By t.CreatedMonth Order by t.CreatedMonth

Upvotes: 0

Related Questions