All Forums Database
sakthikrr 53 posts Joined 07/12
30 Jul 2016
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.

 

Sakthi
sakthikrr 53 posts Joined 07/12
31 Jul 2016

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!

Sakthi

You must sign in to leave a comment.