Sql query to get sales by day

Question

Hello the below query gives the following output :

Sales     RestaurantID    Date
100.00      1             2016-01-01 
200.00      1             2016-01-02 
300.00      1             2016-01-03

Instead of that i need to convert the Date values to columns and show total sales under each day so the output should be like this

RestaurantID    2016-01-01  2016-01-02  2016-01-03     
1               100.00      200.00      300.00

Query

select sum(Sales),RestaurantID, dateadd(DAY,0, datediff(day,0, Date)) as Date
from DailySales
group by dateadd(DAY,0, datediff(day,0, Date)),RestaurantID

Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Gurwinder Singh, Dec 27 '16 at 11:52

karan yadav · Answer 1 · 2016-12-28T05:11:39.843

0

declare @pivcol varchar(max)
SELECT @pivcol = COALESCE(@pivcol + ',','') + '[' + date + ']'         
FROM  DailySales

exec('select * from DailySales 
pivot (sum(sales) for date in ('+ @pivcol +'))')

edited Dec 28 '16 at 05:11

answered Dec 27 '16 at 12:00

karan yadav

59
1
6

The data types varchar(max) and date are incompatible in the add operator. – Ayman Dec 27 '16 at 12:09
we are not storing date in @pivcol we are storing string for in clause – karan yadav Dec 27 '16 at 12:11

score 0 · Answer 2 · answered Dec 27 '16 at 12:05

0

try this

 SELECT *
FROM Table_3
PIVOT(SUM(Sales) 
FOR Date IN ([2016-01-01],[2016-01-02],[2016-01-03])) AS PVTTable

OUTPUT:

answered Dec 27 '16 at 12:05

Genish Parvadia

1,437
3
17
30

Sql query to get sales by day

2 Answers2