SQL loop with data aggregation

Question

I'm using MySql 5.5.38 and got a following table (this is just a snippet, real one contains ~500 products):

+------+-------+----------+-------+--------+
| id   | date  | product  | type  | sales  |
+------+-------+----------+-------+--------+
| 4513 | 14958 | XXXXXXX  |     1 |      3 |
| 4514 | 14958 | XXXXXXX  |     2 |      5 |
| 4519 | 14958 | YYYYYYY  |     1 |     10 |
| 4531 | 14958 | YYYYYYY  |     3 |    150 |
| 4534 | 14959 | ZZZZZZZ  |     1 |      5 |
| 4536 | 14959 | ZZZZZZZ  |     2 |      5 |
|+------+-------+----------+-------+--------+

from which I need to generate daily sales volume by product:

+-------+----------+----------+----------+
| date  | XXXXXXX  | YYYYYYY  | ZZZZZZZ  |
+-------+----------+----------+----------+
| 14958 |        8 |      110 |        0 |
+-------+----------+----------+----------+
| 14959 |        0 |        0 |       10 |
|+------+----------+----------+----------+

The result table has to look exactly like above - with products in columns and days in row.

Just looking for some pointers, not a working solution. Thanks, Pawel.

you can do it in one query, use cases for your xxxx,yyyy,xxx and just sum, and group by. — boomoto, Oct 08 '14 at 18:32
you want to do sum(sales) and group by the product and order by the date — Luo Sen, Oct 08 '14 at 18:34
Might this be a pivot table? http://stackoverflow.com/questions/7674786/mysql-pivot-table — Jim Dennis, Oct 08 '14 at 19:39

Abhishek Gupta · Answer 1 · 2014-10-10T10:49:32.473

1

For dynamic aggregation, use GROUP_CONCAT with CONCAT

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT 
    CONCAT('SUM(CASE WHEN product= "', 
    product, '" THEN sales ELSE 0 END) AS '
    , product))
INTO @sql
FROM
  my_table;

SET @sql = CONCAT('SELECT date, ', @sql, ' 
                  FROM my_table 
                   GROUP BY date');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Use conditional case with group by (for static)

SELECT  s.date,

SUM(
CASE 
WHEN s.product='XXXXXXX' 
THEN s.sales
ELSE 0 
END
) AS 'XXXXXXX',

SUM(
CASE 
WHEN s.product='YYYYYYY' 
THEN s.sales
ELSE 0 
END
) AS 'YYYYYYY',

SUM(
CASE 
WHEN s.product='ZZZZZZZ' 
THEN s.sales
ELSE 0 
END
) AS 'ZZZZZZZ'

FROM my_table s
GROUP BY s.date;

edited Oct 10 '14 at 10:49

answered Oct 08 '14 at 18:55

Abhishek Gupta

4,066
24
27

@pawel: Was this helpful ? – Abhishek Gupta Oct 08 '14 at 19:01
The data I provided is obviously a snippet of the table. The acutal one contains a couple of hundreds of products. So, hardcoded cases won't get me as far as I hoped. – Pawel Pogorzelski Oct 08 '14 at 19:04
@PawelPogorzelski: I guess now it should help. – Abhishek Gupta Oct 08 '14 at 19:58
@Rokcse thanks, coudn't get GROUP_CONCAT working so I actually wrote a Java snippet that genrated conditional case queries you suggested. +1 – Pawel Pogorzelski Oct 09 '14 at 20:15

SQL loop with data aggregation

1 Answers1

Linked