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


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


source


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


source







All Articles