Reputation: 329
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?
Upvotes: 2
Views: 89
Reputation: 25862
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'
note: having clause re evaluates the result set so you can reference type in the having to include the results from your query
Upvotes: 2