I have a 'order' table where the columns pretty much look like this:
| order_id | shop_id | order_total | currency | created_at |
Now I want to make a query that returns a table that looks like this:
| interval | currency | sum |
If the interval is a date interval, the currency is the currency, and the amount is the sum of all orders in this interval of this currency. For example, it could be:
| 2012-08-12-2012-09-12 | EUR | 540922 | | 2012-08-12-2012-09-12 | DKK | 43215 | | 2012-09-12-2012-10-12 | EUR | 123643 | | 2012-09-12-2012-10-12 | DKK | 0 |
I already have a query that defines the currency, but I don’t know how to get it to take into account the currency .. any ideas?
Here is an example of the query I'm using now. The real request, of course, is generated by the code, this is just an example.
SELECT CASE WHEN created_at BETWEEN '2012-08-12' AND '2012-09-12' THEN '2012-08-12-2012-09-12' WHEN created_at BETWEEN '2012-09-12' AND '2012-10-12' THEN '2012-09-12-2012-10-12' WHEN created_at BETWEEN '2012-10-12' AND '2012-10-14' THEN '2012-10-12-2012-10-14' end AS intrvl, Sum(order_total) FROM `order` o WHERE shop_id = 4 AND created_at BETWEEN '2012-08-12' AND '2012-11-17' GROUP BY CASE WHEN created_at BETWEEN '2012-08-12' AND '2012-09-12' THEN '2012-08-12-2012-09-12' WHEN created_at BETWEEN '2012-09-12' AND '2012-10-12' THEN '2012-09-12-2012-10-12' WHEN created_at BETWEEN '2012-10-12' AND '2012-10-14' THEN '2012-10-12-2012-10-14' end
/ Morten
source share