Mysql if-clause sequence

Question

I have a table of transactions like so. 4 fields: time id type money

One of the types has 3 different subtypes (21,22,23), from which I want to create additional types based on the ids and then calculate money per date and type. I am doing it like so.

select date(time), sum(money),
case
when type=2 and id<100 then type=21
when type=2 and id between 100 and 499 then type=22
when type=2 and id>=500 then type=23
when type<>2 then type
end as type
from transactionsTable
where type in (1,2,3,4,21,22,23) and date(time)>='2014-11-01' group by 1,3

One of the weird results is that I get a type=0, which I filtered out within where clause. The second weird thing is that I don't get newly created types 21,22,23 whatsoever!

Could anyone point me into the right direction please?

Answer 1

you cannot reference a type like that in a where or set a type without some sort of insert. what you can do though is use the result from this query to write a query to return what you want.

SELECT  DATE(time), SUM(money),
        CASE
            WHEN type=2 AND id<100 
                THEN 21
            WHEN type=2 AND id BETWEEN 100 AND 499 
                THEN 22
            WHEN type=2 AND id>=500 
                THEN 23
            WHEN type<>2 
                THEN type
        END AS type
FROM transactionsTable
GROUP BY 1,3
HAVING type IN (1,2,3,4,21,22,23) AND DATE(time)>='2014-11-01' 

SIMPLIFIED FIDDLE DEMO

note: having clause re evaluates the result set so you can reference type in the having to include the results from your query