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

Solution

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

Comments

[Morten Jensen]

Alright, that works, but now I get two rows with null as interval, because it counts all orders outside the interval and sets interval as null for those.. is there a way to exclude those from the query?

[Ian Kenney]

just make sure your where clause has the correct created_at range - it has 2012-08-12 to 2012-11-17 but the query only looks at ranges to 2012-08-12 to 2012-10-14