SQL (Teradata): Removing NULLs caused by GroupBy / CASE Statement

Question

I'm having a problem with NULLs showing up in my results. It's because of how I'm using my Group By & CASE Statement with, "ItemDamagedStatus". One solution could be to break out those CASE Statement items and do a JOIN to the same table. However, when I did that some data was dropped out.

The query below is the one actually giving me the correct numbers. I just want it rolled up to a single line based on: Product/Market/Group1.

Thoughts? Questions?

SELECT   t1.Product
        , t1.Market 
        , t1.Group1                                            
        , COUNT(DISTINCT t1.ItemID ||'-'||t1.Date1) AS StoredMth
        , CASE WHEN t1.ItemDamagedStatus = 'C' THEN COUNT(DISTINCT t1.ItemID ||'-'|| t1.Date1) END AS CompleteDmgMth
        , CASE WHEN t1.ItemDamagedStatus = 'P' THEN COUNT(DISTINCT t1.ItemID ||'-'|| t1.Date1) END AS PartialDmgMth
        , CASE WHEN t1.ItemDamagedStatus = 'N' THEN COUNT(DISTINCT t1.ItemID ||'-'|| t1.Date1) END AS NotDmgMth
        , CASE WHEN t1.ItemRepairStatus = 'Y' THEN COUNT(DISTINCT t1.ItemID ||'-'|| t1.Date1) END AS RepairMth
FROM  MainDatabase.Items t1
WHERE  t1.Date1 BETWEEN '2017-01-01' AND '2017-12-31'
GROUP BY      t1.Product
            , t1.Market 
            , t1.Group1  
            , t1.ItemDamagedStatus
            , t1.ItemRepairStatus

Results I'm getting:

Product Market Group1 StoredMth CompleteDmgMth PartialDmgMth NotDmgMth  RepairMth
Car     North  Y      950       50             NULL          NULL       75
Car     North  Y      NULL      NULL           100           NULL       NULL
Car     North  Y      NULL      NULL           NULL          800        NULL
Car     North  N      165       NULL           75            NULL       10
Car     North  N      NULL      NULL           NULL          90         NULL
Car     South  Y      1400      500            NULL          NULL       800
Car     South  Y      NULL      NULL           NULL          900        NULL

Results I want:

Product Market Group1 StoredMth CompleteDmgMth PartialDmgMth NotDmgMth  RepairMth
Car     North  Y      950       50             100           800        75
Car     North  N      165       NULL           75            90         10
Car     South  Y      1400      500            NULL          900        800

(Just a followup in-case this throws anyone off or they try to merge some values... Yes: CompleteDmgMth + PartialDmgMth + NotDmgMth = StoredMth, but it's not super accurate all the time in our data so we use two different methods.)

I apologize if something looks odd or framed incorrectly, it's my first time posting here.

score 3 · Accepted Answer · answered Jan 31 '18 at 16:49

Use aggregation, but not on all the columns. You can nest the CASE expression in the COUNT(DISTINCT):

SELECT t1.Product, t1.Market, t1.Group1,                                        
        COUNT(DISTINCT t1.ItemID || '-' || t1.Date1) AS StoredMth
        COUNT(DISTINCT CASE WHEN t1.ItemDamagedStatus = 'C' THEN t1.ItemID || '' || t1.Date1) END) AS CompleteDmgMth
        COUNT(DISTINCT CASE WHEN t1.ItemDamagedStatus = 'P' THEN t1.ItemID || '' || t1.Date1 END) AS PartialDmgMth
        COUNT(DISTINCT CASE WHEN t1.ItemDamagedStatus = 'N' THEN t1.ItemID || '-' || t1.Date1 END) AS NotDmgMth
        COUNT(DISTINCT CASE WHEN t1.ItemRepairStatus = 'Y' THEN t1.ItemID || '-' || t1.Date1 END) AS RepairMth
FROM  MainDatabase.Items t1
WHERE  t1.Date1 BETWEEN '2017-01-01' AND '2017-12-31'
GROUP BY t1.Product, t1.Market, t1.Group1;

Excellent! Thank you Gordon, worked just as I needed! Completely forgot that you can nest cases in aggregate functions, haha. — CHS_SQL_SE, Jan 31 '18 at 17:09

score 0 · Answer 2 · answered Jan 31 '18 at 16:49

You could use MAX to capture only the non-null values instead of grouping on those fields:

SELECT derived_table.Product,
derived_table.Market
, derived_table.Group1
, MAX(derived_table.StoredMth) as StoredMth
, MAX(derived_table.CompleteDmgMth) as CompleteDmgMth
, MAX(derived_table.PartialDmgMth) as PartialDmgMth
, MAX(derived_table.NotDmgMth) as NotDmgMth
, MAX(derived_table.RepairMth) as RepairMth
FROM (

SELECT   t1.Product
        , t1.Market 
        , t1.Group1                                            
        , COUNT(DISTINCT t1.ItemID ||'-'||t1.Date1) AS StoredMth
        , CASE WHEN t1.ItemDamagedStatus = 'C' THEN COUNT(DISTINCT t1.ItemID ||'-'|| t1.Date1) END AS CompleteDmgMth
        , CASE WHEN t1.ItemDamagedStatus = 'P' THEN COUNT(DISTINCT t1.ItemID ||'-'|| t1.Date1) END AS PartialDmgMth
        , CASE WHEN t1.ItemDamagedStatus = 'N' THEN COUNT(DISTINCT t1.ItemID ||'-'|| t1.Date1) END AS NotDmgMth
        , CASE WHEN t1.ItemRepairStatus = 'Y' THEN COUNT(DISTINCT t1.ItemID ||'-'|| t1.Date1) END AS RepairMth
FROM  MainDatabase.Items t1
WHERE  t1.Date1 BETWEEN '2017-01-01' AND '2017-12-31'
GROUP BY      t1.Product
            , t1.Market 
            , t1.Group1  
            , t1.ItemDamagedStatus
            , t1.ItemRepairStatus) as derived_table
GROUP BY derived_table.Product,
derived_table.Market
, derived_table.Group1;

That was fast Greg, thanks! It *mostly* worked, I know how I could fix the problem that did arise though. (The StoredMth's MAX was the problem, it shouldn't be the max if the group by includes ItemDamagedStatus/ItemRepairStatus -- bunch of ways to fix it, but one quick way is another derived table specifically for StoredMth.) — CHS_SQL_SE, Jan 31 '18 at 17:08

SQL (Teradata): Removing NULLs caused by GroupBy / CASE Statement

2 Answers2