SQL help case statement

mc
mc Dataiku DSS Core Designer, Registered Posts: 6

Greetings all, Obviously there is a syntax error, 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

  • JeanBaptiste
    JeanBaptiste Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 16 ✭✭

    Hi @mc
    ,

    I don't think you can output three different fields in one case statement.

    From what I understand, you can have only one of the three values (splind1, splindnot1, splind_all) for a row.

    You need to create the three fields using one case statement for each one of them, computing the value when it satisfies the condition and specifying a null value when it doesn't.

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

    Thanks for ur response. Okay!

Setup Info
    Tags
      Help me…