Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Greetings all, there is a syntax error in the below, is there some one who can correct?
I am expecting a result set with columns acct, name, country, splind1, splindnot1, splind_all.
Much appreciated!
SELECT `acct`, `name`,`country`,
case
when `splind = '1'
then
sum((`transactions`)/`MV`)*100 as "splind1"
when `splind` != '1'
then
sum((`transactions`)/`MV`)*100 as "splindnot1"
else
sum((`transactions`)/`MV`)*100 as "splind_all"
FROM `twoyears`
group by
`acct`, `name`,`country`
You canโt return 3 values with a single CASE statement. Each case statement returns one value hence you must have a single alias and you are missing the end. Something like this should work:
SELECT `acct`, `name`,`country`,
case
when `splind = '1'
then
sum((`transactions`)/`MV`)*100
when `splind` != '1'
then
sum((`transactions`)/`MV`)*100
else
sum((`transactions`)/`MV`)*100
End as "splind_all"
FROM `twoyears`
group by
`acct`, `name`,`country`
Thanks for ur response. Actually I need to identify the 3 columns separately based on the 3 conditions and ideally in one record as adjacent columns. Probably Case-When is not the syntax I should use. Any suggestions is appreciated .
Well you can have 3 case statements and 3 columns but your case statement logic is not correct because you are doing the same math on all 3 cases so I am not really sure what you are trying to achieve.
SELECT `acct`, `name`,`country`,
case when `splind = '1' then sum((`transactions`)/`MV`)*100 end as "splind1",
case when `splind` != '1' then sum((`transactions`)/`MV`)*100 end as "splindnot1",
case when `splind` IS NULL then sum((`transactions`)/`MV`)*100 end as "splind_all"
FROM `twoyears`
group by
`acct`, `name`,`country`
Thanks a bunch Turribeach, My head got jumbled up - I solved my case with left joins. Actually I am starting SQL again after a gap. Thanks so much for ur answers in this forum. My case was that I had to preserve all the records and see the behavior of splind on each record. Thanks again