SQL help - Case Statement

Options
mc
mc Dataiku DSS Core Designer, Registered Posts: 6

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`

Tagged:

Answers

  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,710 Neuron
    Options

    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`

  • mc
    mc Dataiku DSS Core Designer, Registered Posts: 6
    Options

    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 .

  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,710 Neuron
    Options

    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`

  • mc
    mc Dataiku DSS Core Designer, Registered Posts: 6
    Options

    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

Setup Info
    Tags
      Help me…