Post reply

Grouping distinct values by date

  • February 11, 2014 at 3:51 pm

    #285568

    Trying to segregate each of these dates with the exact amount of type of products sold . I have to query it against this table, i have no control how this table is designed.

    CREATE TABLE widgetsales (ID int identity(1,1), date datetime, typesold int, amount numeric(18, 4))

    INSERT INTO widgetsales values('6/30/13', 1, 27.95)

    INSERT INTO widgetsales values('5/20/13', 1, 21.85)

    INSERT INTO widgetsales values('5/22/13', 2, 27.90)

    INSERT INTO widgetsales values('12/11/12', 2, 23.95)

    INSERT INTO widgetsales values('6/13/13', 3, 24.90)

    INSERT INTO widgetsales values('6/30/13', 2, 27.95)

    INSERT INTO widgetsales values('5/20/13', 2, 21.85)

    INSERT INTO widgetsales values('5/22/13', 1, 27.90)

    INSERT INTO widgetsales values('12/11/12',1, 23.95)

    INSERT INTO widgetsales values('6/13/13', 3, 24.90)

    INSERT INTO widgetsales values('6/30/13', 3, 27.95)

    INSERT INTO widgetsales values('5/20/13', 3, 21.85)

    INSERT INTO widgetsales values('5/22/13', 2, 27.90)

    INSERT INTO widgetsales values('12/11/12', 1, 23.95)

    INSERT INTO widgetsales values('6/13/13', 1, 24.90)

    here's how my report needs to read:

    date count_1 count_2 count_3

    ----------------------- ----------------- ----------------- -----------------

    2012-12-11 00:00:00.000 2 1 0

    2013-05-20 00:00:00.000 1 1 1

    2013-05-22 00:00:00.000 1 2 0

    2013-06-13 00:00:00.000 1 0 2

    2013-06-30 00:00:00.000 1 1 1

    doing this query below will give me a total count of 3 for each column among each date, so there is another aggregate function i seem to not be able to coordinate into this

    Select date, count(typesold) as count_1 ,count(typesold) as count_2,count(typesold) as count_3 from widgetsales where date in ('2012-12-11','2013-05-20','2013-05-22','2013-6-13','2013-6-30') group by date

  • February 11, 2014 at 4:16 pm

    #1688153

    SELECT date,

    SUM(CASE WHEN typesold = 1 THEN 1 ELSE 0 END) AS count_1,

    SUM(CASE WHEN typesold = 2 THEN 1 ELSE 0 END) AS count_2,

    SUM(CASE WHEN typesold = 3 THEN 1 ELSE 0 END) AS count_3

    FROM widgetsales

    GROUP BY date

    ___________________________
    Do Not Optimize for Exceptions!

  • February 11, 2014 at 5:09 pm

    #1688167

    For some performance improvement you could pre-aggregate your data. Note that I'm suming the counts but is still a count.

    SELECT [date],

    SUM( CASE WHEN typesold = 1 THEN amount ELSE 0 END) count_1,

    SUM( CASE WHEN typesold = 2 THEN amount ELSE 0 END) count_2,

    SUM( CASE WHEN typesold = 3 THEN amount ELSE 0 END) count_3

    FROM(

    SELECT [date],

    typesold,

    COUNT(amount) amount

    FROM #widgetsales

    GROUP BY [date],

    typesold)x

    GROUP BY [date]

    For more about this, check the following article: http://qa.sqlservercentral.com/articles/T-SQL/63681/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply