StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I'm posting this as an answer as it may be quite large for a comment. Thanks to Mark for pointing me in the right direction. Mark's answer worked but meant a schema change to the <em>calendar</em> table which I wasn't keen on, as I may need the report to be more flexible in future (e.g. group by <em>product_id</em>)</p> <p>This works - however it may not be elegant. I'll leave this question "unanswered" for a few days to see if anyone can come up with a better solution.</p> <p>Additional schema and data (addition of a products table):</p> <pre><code>CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(255) ); INSERT INTO products (id, name) VALUES (1, 'Widget'), (2, 'Midget'), (3, 'Gidget'); </code></pre> <p>Now using this query I get the answer I want:</p> <pre><code>SELECT cal.date AS date, currency.name AS currency, products.name AS product, IFNULL(SUM(orders.order_value),0) AS total_value FROM orders RIGHT JOIN ( SELECT cal.datefield AS date, cur.id AS currency, prod.id AS product FROM calendar cal CROSS JOIN currency cur CROSS JOIN products prod ) cal ON (DATE(orders.order_date) = cal.date) AND orders.currency_id = cal.currency AND orders.product_id = cal.product JOIN currency ON cal.currency = currency.id JOIN products ON cal.product = products.id WHERE (cal.date BETWEEN (SELECT MIN(DATE(order_date)) FROM orders) AND (SELECT MAX(DATE(order_date)) FROM orders)) GROUP BY date, cal.currency,cal.product </code></pre> <p>This gives me all data points for all days, or zero if they don't exist.</p> <pre><code>+------------+-----------+--------+-------------+ | date | currency | product| total_value | +------------+-----------+--------+-------------+ | 2009-08-15 | Euro | Widget | 40.97 | | 2009-08-15 | Euro | Midget | 40.97 | | 2009-08-15 | Euro | Gidget | 0.00 | | 2009-08-15 | US Dollar | Widget | 12.50 | | 2009-08-15 | US Dollar | Midget | 12.50 | | 2009-08-15 | US Dollar | Gidget | 0.00 | | 2009-08-15 | CA Dollar | Widget | 122.60 | | 2009-08-15 | CA Dollar | Midget | 0.00 | | 2009-08-15 | CA Dollar | Gidget | 0.00 | | 2009-08-16 | Euro | Widget | 0.00 | | 2009-08-16 | Euro | Midget | 0.00 | | 2009-08-16 | Euro | Gidget | 0.00 | | 2009-08-16 | US Dollar | Widget | 0.00 | | 2009-08-16 | US Dollar | Midget | 0.00 | | 2009-08-16 | US Dollar | Gidget | 0.00 | | 2009-08-16 | CA Dollar | Widget | 0.00 | | 2009-08-16 | CA Dollar | Midget | 0.00 | | 2009-08-16 | CA Dollar | Gidget | 0.00 | | 2009-08-17 | Euro | Widget | 0.00 | | 2009-08-17 | Euro | Midget | 0.00 | | 2009-08-17 | Euro | Gidget | 0.00 | | 2009-08-17 | US Dollar | Widget | 12.50 | | 2009-08-17 | US Dollar | Midget | 12.50 | | 2009-08-17 | US Dollar | Gidget | 0.00 | | 2009-08-17 | CA Dollar | Widget | 0.00 | | 2009-08-17 | CA Dollar | Midget | 122.60 | | 2009-08-17 | CA Dollar | Gidget | 0.00 | | 2009-08-18 | Euro | Widget | 40.97 | | 2009-08-18 | Euro | Midget | 40.97 | | 2009-08-18 | Euro | Gidget | 0.00 | | 2009-08-18 | US Dollar | Widget | 0.00 | | 2009-08-18 | US Dollar | Midget | 0.00 | | 2009-08-18 | US Dollar | Gidget | 0.00 | | 2009-08-18 | CA Dollar | Widget | 122.60 | | 2009-08-18 | CA Dollar | Midget | 122.60 | | 2009-08-18 | CA Dollar | Gidget | 0.00 | +------------+-----------+--------+-------------+ </code></pre> <p>This uses a JOIN on a subquery which I think is not very performant, but it works on this small dataset - I will generate some more data and see how it goes.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload