MS SQL: Group by day and count

Posted on 2007-10-16
Last Modified: 2008-05-27

I have the following query:
select   date_added,
      (select count(*) from redeem R where whisky_id = 1 and R.date_added = redeem.date_added) as [Item1],
      (select count(*) from redeem R where whisky_id = 2 and R.date_added = redeem.date_added) as [Item2],
      (select count(*) from redeem R where whisky_id = 6 and R.date_added = redeem.date_added) as [Item3]
from redeem
group by date_added

the data out is something like:

date_added          | Item1 | Item2 | Item3

2007-01-22 12:13.05 | 1     | 4     | 0
2007-01-22 12:15.05 | 0     | 9     | 4
2007-01-22 17:12.05 | 1     | 4     | 5
2007-01-26 00:14.05 | 0     | 1     | 4
2007-01-28 10:15.05 | 0     | 1     | 4

Now what I wanted to do was group the results by day. So the first 3 records would be combined because they are on the same day. How would I go about doing this?
Any help would be greatly appreciated.

Question by:bLUE
    LVL 17

    Expert Comment

    by:Shanmuga Sundaram
    use left(convert(varchar(fieldname),111),2)
    LVL 68

    Accepted Solution

    SELECT CONVERT(VARCHAR(10), date_added, 120) AS [DateAdded],
        SUM(CASE WHEN whiskey_id = 1 THEN 1 ELSE 0 END) AS [Item1],
        SUM(CASE WHEN whiskey_id = 2 THEN 1 ELSE 0 END) AS [Item2],
        SUM(CASE WHEN whiskey_id = 6 THEN 1 ELSE 0 END) AS [Item3]
    FROM redeem
    WHERE whiskey_id IN (1, 2, 6)
    GROUP BY CONVERT(VARCHAR(10), date_added, 120)
    LVL 1

    Author Comment


    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now