M O H M O H - 6 months ago 10
SQL Question

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.

Answer

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]