CODEWITHSUNDEEP
sqlcount
yoda
yoda

Reputation: 569

Include those groups which are not present as zero

I have a query as below which outputs sum only when records of column booklet_type are present. It groups results on types like 'GR' and 'PI'.

For instance, if GR has end_leaf_no and start_leaf_no as '5' and '4' and same for PI then records will be displayed on matching booking_date like,

GR 2

PI 2

But, these does not return any records for 0 occurences of booklets of specific type. I want it as,

GR 0

PI 0

How do I go about accomplishing this result? I tried case when in select clause but to no avail.

Thanks. 

Select Booklet_type, SUM(End_Leaf_No -  Start_Leaf_No +1) as No_of_Coupons  

from Coupon_Sale 

where date_format(Booklet_Sale_Date, '%Y%m') = :ccyymm 
      and Booklet_type = “GR” or “PI” 
group by Booklet_Type

Upvotes: 0

Views: 62

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You can do this with a left outer join and a subquery that generates the rows you want. The following works in most databases:

Select driver.Booklet_type, SUM(cs.End_Leaf_No -  cs.Start_Leaf_No +1) as No_of_Coupons  
from (select 'GR' as Booklet_type union all
      select 'PI'
     ) driver left outer join
     Coupon_Sale cs
     on driver.Booklet_Type = cs.Booklet_Type and
        date_format(cs.Booklet_Sale_Date, '%Y%m') = :ccyymm and
        cs.Booklet_type in ('GR', 'PI')
group by driver.Booklet_Type;

To make this work, I moved the where conditions into the on clause.

Upvotes: 1

Related Questions