SQL loop with data aggregation
I am using MySql 5.5.38 and got the following table (this is just a snippet, the 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 by product:
+-------+----------+----------+----------+
| date | XXXXXXX | YYYYYYY | ZZZZZZZ |
+-------+----------+----------+----------+
| 14958 | 8 | 110 | 0 |
+-------+----------+----------+----------+
| 14959 | 0 | 0 | 10 |
|+------+----------+----------+----------+
The results table should look exactly the same as above - with products in columns and days in a row.
Just look for some pointers, not a working solution. Thank you, Pavel.
0
source to share
1 answer
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;
+1
source to share