包括那些不存在为零的组

问题描述

我有一个如下的查询，只有当列 booklet_type 的记录存在时才输出sum。它将类型结果分组为GR和PI。

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'.

例如，如果GR具有 end_leaf_no 和 start_leaf_no 作为'5'和'4'，并且 PI 也一样，则记录将显示在匹配的booking_date上，例如

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

如何完成这个结果？我试过case在select子句，但没有效果。

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

谢谢。

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

推荐答案

您可以使用左外连接和生成所需行的子查询。以下适用于大多数数据库：

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.

这篇关于包括那些不存在为零的组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！