Displaying product sales for each day in SQL
I have a problem, I cannot find a solution.
I have a table "SELLS" with all sales of a store, and I want to display how many sales for each product each day of the period.
For example:
| DATE | NAME | QTY |
|------------|----------|-----|
| 2014-07-03 | Coca | 1 |
| 2014-07-03 | Fanta | 1 |
| 2014-07-03 | Orangina | 5 |
| 2014-07-03 | Coca | 3 |
| 2014-07-04 | Coca | 2 |
| 2014-07-05 | Coca | 4 |
| 2014-07-05 | Fanta | 1 |
| 2014-07-05 | Juice | 2 |
The display I want is:
| NAME | TOTAL | 2014-07-03 | 2014-07-04 | 2014-07-05 |
|------------|--------|------------|-------------|-------------|
| Coca | 10 | 4 | 2 | 4 |
| Fanta | 2 | 1 | 0 | 1 |
| Orangina | 1 | 1 | 0 | 0 |
| Juice | 1 | 0 | 0 | 1 |
The user will specify the period they want to display, so I need to use the BETWEEN function for the date.
I am trying to use the PIVOT function, but I am still not familiar with it
Edit: I am using SQL Server 2012.
Many thanks for your help.
+3
BlackAlpha
source
to share
2 answers
Create table temp
(
tdate date,
name varchar(10),
qty int
)
insert into temp values (getdate(),'A',10)
insert into temp values (getdate(),'B',20)
insert into temp values (getdate(),'C',20)
insert into temp values (getdate(),'A',20)
insert into temp values (getdate(),'B',30)
insert into temp values (getdate(),'C',40)
insert into temp values (getdate()+1,'A',20)
insert into temp values (getdate()+1,'B',30)
insert into temp values (getdate()+1,'C',40)
select * from
( select tdate, name, qty
from temp
) src
pivot (
sum(qty)
for tdate in ([2015-05-12],[2015-05-13])
) piv;
+1
Amnesh goel
source
to share
Doesn't that do the trick?
SELECT sum(QTY), DATE, NAME FROM SELLS WHERE DATE BETWEEN .... GROUP BY DATE,NAME
PS: added inter clause
-1
zeratulmdq
source
to share