Aggregate details into summary - case statement

Database
Enthusiast

Aggregate details into summary - case statement

Dears,

We need to summarize the below detail data as expected; but somewhere we are missing something and not getting the proper aggregate. Pls help.

Detailed data:

ID          GSM     LL      Loyalty Points

aaa        ?          ?       100

bbb        200      ?       200

ccc         ?         10     10

ddd        60        ?       60

ddd         ?         120   120

Expected Summary Data:

Category        Loyalty Points

Prepaid           100

GSM Only       200

LL Only          10

GSM + LL      180

And the query we wrote:

SEL 
CASE WHEN GSM IS NOT NULL AND LL IS NOT NULL THEN 'GSM+LL'
WHEN GSM IS NOT NULL AND LL IS NULL THEN 'GSM'
WHEN GSM IS NULL AND LL IS NOT NULL THEN 'LL'
WHEN GSM IS NULL AND LL IS NULL THEN 'PREPAID' END CAT
,SUM(REDEEMED)

FROM (

SELECT
ID
,SUM(CASE WHEN AWARD_TYPE_CD = 'GSM' THEN REDEMPTION_POINTS END) GSM
,SUM(CASE WHEN AWARD_TYPE_CD = 'LL' THEN REDEMPTION_POINTS END) LL
,SUM(REDEMPTION_POINTS) AS REDEEMED
FROM Loyalty_Tbl

WHERE MONTH_ID = '201606'

GROUP BY 1
) A

GROUP BY 1

Above query is gives below result which is not expected:

Category       Loyalty Points

Prepaid         100

GSM Only      260

LL Only         130

How to re-write the case statement? Pls help.

Thanks in advance.




1 REPLY
Enthusiast

Re: Aggregate details into summary - case statement

Updated detailed table with missing column:

ID          AWARD_TYPE_CD          GSM     LL      Loyalty Points

aaa             ?                                ?         ?       100

bbb           GSM                           200       ?       200

ccc             LL                               ?         10     10

ddd         GSM                              60         ?       60

ddd            LL                                ?         120   120

Thanks!