Reputation: 13
I have a single table of data (simplified below)
| id | date | product |
|----|-------------|---------|
| 1 | 2019-01-02 | prod 1 |
| 2 | 2019-01-02 | prod 2 |
| 3 | 2019-01-22 | prod 1 |
| 4 | 2019-02-02 | prod 1 |
| 5 | 2019-02-02 | prod 1 |
| 6 | 2019-03-02 | prod 1 |
I would like the end result to look like this...
| product | 2019-01 | 2019-02 | 2019-03 |
|---------|---------|---------|---------|
| prod 1 | 2 | 3 | 0 |
| prod 2 | 1 | 0 | 0 |
In short, I would like to loop over all products between a date range (all dates are contained in a the single table). When there's no result for a month return 0. I believe COALESCE may be the way to tackle this.
I've attempted this two separate ways... return all data in a single query and loop over that data, but I if there's no data it never returns a string or something I can use (null).
SELECT
product_name,
count(product_name) AS count
DATE_FORMAT(date,'%Y-%m') AS date
FROM products
GROUP BY DATE_FORMAT(date,'%Y-%m'), company_name
ORDER BY date;
| product | date | count |
|---------|---------|-------|
| prod 1 | 2019-01 | 2 |
| prod 2 | 2019-01 | 1 |
| prod 1 | 2019-02 | 3 |
| prod 2 | 2019-02 | 0 | <--- this row doesn't return
Alternatively I've tried looping over all products and then individually looping through the dates but again no data is returned if there's no date in the table so my table would become skewed.
I've seen others store dates in a different table and then loop over that, this felt like overkill as I have all dates in this table, so I tried joining the table onto itself but that didn't work either.
Thanks in advance for any help.
Upvotes: 1
Views: 175
Reputation: 352
This basically is a pivot table.
A nice tutorial on how to achieve this can be found here: http://www.artfulsoftware.com/infotree/qrytip.php?id=78
Here is your dynamic solution for pivot table. Just use query suggested by @Nick and make temporary table of that
create temporary table tmp as
SELECT
p.product_name,
count(p2.product_name) AS count,
d.date
FROM (SELECT DISTINCT DATE_FORMAT(date,'%Y-%m') AS date
FROM products) d
CROSS JOIN (SELECT DISTINCT product_name
FROM products) p
LEFT JOIN products p2 ON DATE_FORMAT(p2.date,'%Y-%m') = d.date AND p2.product_name = p.product_name
GROUP BY p.product_name, d.date
ORDER BY p.product_name, d.date;
Now here is dynamic SP that will convert Row into column regard any new product come in future.
delimiter $$
DROP PROCEDURE IF EXISTS pivot $$
CREATE PROCEDURE pivot(IN schema_name VARCHAR(64)
, IN table_name VARCHAR(64)
, IN id_name VARCHAR(64)
, IN key_name VARCHAR(64)
, IN value_name VARCHAR(64))
pivot:BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @error := 1;
SET @error := 0;
SELECT MAX(character_maximum_length)
INTO @maxlen
FROM information_schema.columns
WHERE table_schema = schema_name
AND table_name = table_name
AND column_name = key_name
AND data_type IN ('char', 'varchar');
SET @maxlen = IFNULL(@maxlen,500);
IF @error OR !@maxlen OR @maxlen IS NULL THEN
SELECT '@error OR @maxlen=0 OR @maxlen IS NULL', @error, @maxlen;
LEAVE pivot;
END IF;
DROP TEMPORARY TABLE IF EXISTS temp_pivot;
SET @sql := CONCAT('CREATE TEMPORARY TABLE temp_pivot (key_name VARCHAR(',
@maxlen,
')) ENGINE=Memory SELECT DISTINCT `',
key_name,
'` key_name FROM `',
schema_name,
'`.`',
table_name,
'`;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
SELECT GROUP_CONCAT(CONCAT( ', MAX(CASE `',
key_name,
'` WHEN ''',
temp_pivot.key_name,
''' THEN `',
value_name,
'` else 0 END) `',
temp_pivot.key_name,
'`') SEPARATOR '')
INTO @sql
FROM temp_pivot;
DROP TEMPORARY TABLE IF EXISTS Pivot_Check;
SET @sql := CONCAT('CREATE TEMPORARY TABLE Pivot_Check AS ','SELECT `',
id_name,
'`',
@sql,
' FROM `',
schema_name,
'`.`',
table_name,
'` GROUP BY `',
id_name,
'`;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
SET @error := NULL;
SET @maxlen := NULL;
SET @sql := NULL;
END $$
delimiter
and just call SP
call pivot('schema_name','table_name','product','count','date');
And your pivot table ready and just use
select * from Pivot_Check
Upvotes: 0
Reputation: 147286
To get the second result you want you first need to make a CROSS JOIN
between all the distinct values of date
and product_name
in the table. This can then be LEFT JOIN
ed to the table again to get all the values for each product for each month, and the results grouped by product_name and month:
SELECT
p.product_name,
count(p2.product_name) AS count,
d.date
FROM (SELECT DISTINCT DATE_FORMAT(date,'%Y-%m') AS date
FROM products) d
CROSS JOIN (SELECT DISTINCT product_name
FROM products) p
LEFT JOIN products p2 ON DATE_FORMAT(p2.date,'%Y-%m') = d.date AND p2.product_name = p.product_name
GROUP BY p.product_name, d.date
ORDER BY p.product_name, d.date
Output
product_name count date
prod 1 2 2019-01
prod 1 3 2019-02
prod 2 1 2019-01
prod 2 0 2019-02
To make the first result is doable, but requires the use of a stored procedure to dynamically create a pivot table query. It's a lot easier to take the result above and process it in PHP to achieve the same result.
Upvotes: 1