How to display zero rows for non-existent values SQL-CodePudding

I have the below SQL which generates the Account type and their region and type. Currently, my table has only Type 1, Type 2, Type 4, and Type 6. But I would like it to show me all the types: 1,2,3,4,5,6 where 3 and 5 would have zero.

So my results would look like this

select *
from (values ('Account Exec','US','Type 1',12)
           , ('Account Exec','US','Type 2',34)
           , ('Account Exec','US','Type 4',24)
           , ('Account Exec','US','Type 6',32)
           , ('Account Exec','Europe','Type 1',35)
           , ('Account Exec','Europe','Type 4',43)
           , ('Account Exec','Europe','Type 6',45)
           , ('Account Exec','Others','Type 1',23)
           , ('Account Exec','Others','Type 4',23)
           , ('Account Exec','Others','Type 6',54) 
    ) a(TE,REG,ATTAIN,total)

CodePudding user response：

do you mean something like this:

select *
from (values ('Account Exec','US','Type 1',12),
             ('Account Exec','US','Type 2',34),
             ('Account Exec','US','Type 3',0),
             ('Account Exec','US','Type 4',24),
             ('Account Exec','US','Type 5',0),
             ('Account Exec','US','Type 6',32))

CodePudding user response：

You can create the domain of discourse and then left join that with your sample data. Your sample data did not include all types so I hard-coded that one:

with types (attain) as ( values ('Type 1'),('Type 2'),('Type 3'),('Type 4'),('Type 5'),('Type 6') )
    , sample (te, reg, attain, total) as (
        select *
        from (values ('Account Exec','US','Type 1',12)
           , ('Account Exec','US','Type 2',34)
           , ('Account Exec','US','Type 4',24)
           , ('Account Exec','US','Type 6',32)
           , ('Account Exec','Europe','Type 1',35)
           , ('Account Exec','Europe','Type 4',43)
           , ('Account Exec','Europe','Type 6',45)
           , ('Account Exec','Others','Type 1',23)
           , ('Account Exec','Others','Type 4',23)
           , ('Account Exec','Others','Type 6',54) 
        ) a(TE,REG,ATTAIN,total) )
    , te_reg(te, reg, attain) as (
         select distinct s.te, s.reg, t.attain
         from sample s
         cross join types t
 )
select t.te, t.reg, t.attain, coalesce(s.total, 0)
from te_reg t
left join sample s
    using (te, reg, attain)
order by t.te, t.reg, t.attain;

te  reg attain  coalesce
Account Exec    Europe  Type 1  35
Account Exec    Europe  Type 2  0
Account Exec    Europe  Type 3  0
Account Exec    Europe  Type 4  43
Account Exec    Europe  Type 5  0
Account Exec    Europe  Type 6  45
Account Exec    Others  Type 1  23
Account Exec    Others  Type 2  0
Account Exec    Others  Type 3  0
Account Exec    Others  Type 4  23
Account Exec    Others  Type 5  0
Account Exec    Others  Type 6  54
Account Exec    US  Type 1  12
Account Exec    US  Type 2  34
Account Exec    US  Type 3  0
Account Exec    US  Type 4  24
Account Exec    US  Type 5  0
Account Exec    US  Type 6  32

Note that the example is for postgres, so you may have to adjust the syntax a bit. See Fiddle

If you table contains all types you can do a distinct from there instead of enumerating them like I did in types.