February 11, 2014 at 3:51 pm
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
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
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://www.sqlservercentral.com/articles/T-SQL/63681/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply