Displaying products sales for each day in SQL

Question

I have a problem I can't find a solution.

I have a table "SELLS" with all sells of a shop and I want to display how many sales for each product each day of a 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 he wants to display, so I have to use a BETWEEN function for date.

I try with PIVOT function, but I'm still not familiar using it

Edit : I'm using SQL Server 2012.

Thanks a lot for your help.

What RDBMS (SQL Server, Oracle, PostgreSQL, MySQL, etc) are you using? — Politank-Z, May 12 '15 at 15:29
I'm using SQL Server, I edit my question. @Politank-Z what I try display an error so not very useful I think ... On a previous question, the solution was PIVOT but it was for Sums of sales per payments methods — BlackAlpha, May 12 '15 at 15:33
does this help.. http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/ — Abdul Rehman Sayed, May 12 '15 at 15:36
Showing what you've already tried, even if it doesn't work, can help the person answering your question to more precisely understand what you are trying to do, and if you aren't completely wrong, offer a more precise answer. — Politank-Z, May 12 '15 at 15:37
Similar like [here](http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Amnesh Goel, May 12 '15 at 16:01
A better example that will also give you a row total and the dynamic generation of the date column names http://stackoverflow.com/questions/18081086/t-sql-pivot-total-row-and-dynamic-columns — Mike, May 12 '15 at 16:30

score 1 · Accepted Answer · answered May 12 '15 at 16:12

1

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;

answered May 12 '15 at 16:12

Amnesh Goel

2,617
3
28
47

`select convert(CHAR(10), [tdate], 120) as tdate, name, qty ...` for sanity purposes. – dani herrera May 12 '15 at 16:17
i have an error because [2015-05-12] is a varchar and i'm using datetime. It seems cast don't work in that case ? or am I wrong again ? – BlackAlpha May 12 '15 at 16:26
I tried changing *tdate* column datatype to *datetime* and it still worked. Could you please share your table definition? – Amnesh Goel May 12 '15 at 16:35
`CREATE TABLE [dbo].[TICKET_VENTE]( [DATE] [datetime] NULL, [LIBELLE] [varchar](40) NULL, [QTE] [float] NULL)` – BlackAlpha May 12 '15 at 16:39
Well first thing first, why do you have a column with *[Date]* a keyword? and probably the error is in the date format. What is your date format that you have in your table? (DDMMYYYY/ MMDDYYYY/ YYYYMMDD etc) – Amnesh Goel May 12 '15 at 16:42
The table is create automatically by an other software, I can't change that. Here is an example of a value : 2015-03-16 00:00:00.000 (YYYY-MM-DD) – BlackAlpha May 12 '15 at 16:52
hunh... what is the error man.. I guess it should work.. It worked for me... u just copy paste the above code and run it.. it will work – Amnesh Goel May 12 '15 at 16:56
i change the date and it works ... i think it's because 2015-05-13 not appears in my table. It works fine, thanks for your time and your help ! – BlackAlpha May 12 '15 at 16:59

score -1 · Answer 2 · answered May 12 '15 at 15:56

-1

Doesn't this do the trick?

SELECT sum(QTY), DATE, NAME FROM SELLS WHERE DATE BETWEEN .... GROUP BY DATE,NAME

P.S: added the between clause

answered May 12 '15 at 15:56

zeratulmdq

1,484
16
15

Displaying products sales for each day in SQL

2 Answers2