Calculated Fields

[pablovicente]

Hi i'm trying to get a calculated field from mysql but i can't get it to work.
This is my query:

select name,

sum(if((id_action = '1' or id_action = '9' or id_action= '17'),1, 0)) as total_1,

sum(if((id_action = '20' or id_action = '7' or id_action = '15'),1, 0)) as total_2

group by name

Now, what i want is to get the sum of total_1 + total_2 but as they are already calculated ones i can't do: sum(total_1,total_2)
not evan (total_1 + total_2) because i get an error "Unknown colum total_1" any idea?
Thanks in Advance.

[svnfightsvn]

well - you can add them together in the script itself instead of the query ro try to nest the sum() functions:

sum(sum(if((id_action = '1' or id_action = '9' or id_action= '17'),1, 0))+sum(if((id_action = '20' or id_action = '7' or id_action = '15'),1, 0)))

[pablovicente]

grouping the sums functions don't work.........
any other idea?
Thanks.

[svnfightsvn]

Originally posted by pablovicente
grouping the sums functions don't work.........
any other idea?
Thanks.

how about combining the IF statements in the query and then taking the sum:

sum(if((id_action='1' or id_action='9' or id_action='17' or id_action='20' or id_action='7' or id_action = '15'),1, 0))

OR like I said - sum them up in the script itself instead of in the query

[tomhath]

I think it's so obvious you looked right past it:

select name, 
sum(if((id_action = '1' or id_action = '9' or id_action= '17'),1, 0)) as total_1, 

sum(if((id_action = '20' or id_action = '7' or id_action = '15'),1, 0)) as total_2 ,

sum(if((id_action = '1' or id_action = '9' or id_action= '17' or id_action = '20' or id_action = '7' or id_action = '15'),1, 0)) as total_3
...