How to show a grouped Column with a CASE logic

IgneP

Hi, could smb help me on the issue below please:

the below CASE model for 'Royalty add on' is splitting my ORGANIZER 'Live Nation Italia' into 3 columns creating splits also for Autorization and Shipping exp. The idea is to have a sum of all these values and see only 1 column for Live Nation Italia.

I know for sure that the problem is Royalty add on CASE, because if I remove it I see Autorization and Shipping exp for only 1 column. hopes it makes sense.

p.s Also tried, ELSE 0 - but this gives me a 0 instead of the empty cell.

CASE is for : to see the value ONLY Collector tickets for LIVE NATION group organizers:

Live Nation 2 s.r.l.

Live Nation 3 s.r.l.

Live Nation Italia s.r.l

Comcerto Srl

a Collector ticket is the one that has

amCollectorPrice > 1

—

CASE
WHEN (nmOrganizer ='Live Nation 2 s.r.l.' AND amCollectorPrice > 1) OR (nmOrganizer = 'Live Nation 3 s.r.l.' AND amCollectorPrice > 1) OR (nmOrganizer ='Live Nation 6 s.r.l.' AND amCollectorPrice > 1) OR (nmOrganizer ='Live Nation Italia s.r.l' AND amCollectorPrice > 1) OR (nmOrganizer ='Comcerto Srl' AND amCollectorPrice > 1) THEN (SUM(qtTicket)*2.54)
ELSE ''
END

ggenovese

Can you give this a try?

SUM(

CASE

WHEN 
amCollectorPrice > 1
AND nmOrganizer IN (
'Live Nation 2 s.r.l.'
, 'Live Nation 3 s.r.l.'
, 'Live Nation 6 s.r.l.'
, 'Live Nation Italia s.r.l'
,'Comcerto Srl'
)

THEN qtTicket

END

)*2.54

ggenovese

Can you give this a try?

SUM(

CASE

WHEN 
amCollectorPrice > 1
AND nmOrganizer IN (
'Live Nation 2 s.r.l.'
, 'Live Nation 3 s.r.l.'
, 'Live Nation 6 s.r.l.'
, 'Live Nation Italia s.r.l'
,'Comcerto Srl'
)

THEN qtTicket

END

)*2.54

Manasi_Panov

Hi @IgneP,

It should also work with nested CASE statements (CASE inside a CASE), but definitely I agree that wrapping CASE in SUM(), as @ggenovese demonstrated, is the best approach.

SUM(CASE WHEN…) *2.54