CODEWITHSUNDEEP
phpmysqlsql
shin
shin

Reputation: 3649

Date as column (Create report per day)

I have this query which generates the result that i have wanted. I just need to make date into a column

SELECT item, date, SUM(quantity) 
FROM t 
WHERE date between '2015-08-18' and '2015-08-20'
GROUP BY item, date

Here is my SQL FIDDLE

which generates

Output

Result I've wanted

result

Please can anyone give me at least idea on how to achieve the result I've wanted?

Upvotes: 0

Views: 89

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

Something like this using conditional Aggregate

To extract the day from date use DAY() function

SELECT item,
       SUM(case when day(date) = 18 then quantity else 0 end) as `18`,
       SUM(case when day(date) = 19 then quantity else 0 end) as `19`,
       SUM(case when day(date) = 20 then quantity else 0 end) as `20`
FROM t 
WHERE date between '2015-08-18' and '2015-08-20'
GROUP BY item

SQL FIDDLE DEMO

Upvotes: 3

Related Questions