Mysql query case with range, and case with currency

Question

I have a table 'order' 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 looking like this:

| interval | currency | sum |

Where interval is an interval of dates, currency is the currency and sum is the sum of all the orders in that interval, of that currency. Så an example 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 diregards currency, but i dont know how to make it take the currency into consideration.. any ideas?

Heres an example of the query i use now. The real query is of course generated by 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

Answer 1

add currency into your select and group by clauses

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,
       currency, 
       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,
          currency