按月包括空月份 [英] Group by month including empty months

问题描述

我想每月选择所有订单价值。我知道这适用于GROUP BY月份，但只有在有订单的月份。现在我也想要没有订单的月份，所以我得到所有月份。

这是我的查询：

  SELECT SUM（VerkoopfactBedrag）AS bedrag，DATE_FORMAT（VerkoopfactDatum，'％M'）AS date 
 FROM verkoopfacturen 
 WHER Verkoopfact_UserId = 12 
 AND VerkoopfactDatum BETWEEN' 2011-01-30'AND'2011-12-30'
 GROUP BY MONTH（VerkoopfactDatum）
  

因此，当一个月的结果为0时，我希望看到值为0的月份，但现在该月份不显示。

这可能吗？

解决方案

一种方法是创建并填充连续几个月的表。

然后你可以使用该表 OUTER JOIN 。

p>

  drop table if exists all_months; 
 
 create table all_months（a_month_id int unsigned PRIMARY KEY，a_month varchar（20）NOT NULL，UNIQUE KEY`all_months_uidx1`（a_month））; 
 
插入all_months值（1，'January'）; 
插入all_months值（2，'二月'）; 
插入all_months值（3，'March'）; 
插入all_months值（4，'April'）; 
插入all_months值（5，'May'）; 
插入all_months值（6，'六月'）; 
插入all_months值（7，'July'）; 
插入all_months值（8，'August'）; 
插入all_months值（9，'9月'）; 
插入all_months值（10，'10月'）; 
插入all_months值（11，'11月'）; 
插入all_months值（12，'12月'）; 
 
 SELECT SUM（IFNULL（t1.VerkoopfactBedrag，0））AS分段，
 am.a_month AS日期
从
（
 select 
 ifnull（vn.VerkoopfactBedrag，0）as VerkoopfactBedrag，
 cast（DATE_FORMAT（VerkoopfactDatum，'％M'）as char）as mdate 
 FROM verkoopfacturen vn 
 WHERE Verkoopfact_UserId = 12 
 AND VerkoopfactDatum BETWEEN'2011-01-01'AND'2011-12-31'
 GROUP BY DATE_FORMAT（VerkoopfactDatum，'％M'）
）t1 RIGHT OUTER JOIN all_months am on t1.mdate = am.a_month 
 group by am.a_month 
 by a_month_id asc; 
  

PS不确定你是否有反对Oudejaarsavond的消息，但是在12月有31天; - ） p>

I want to select all my order values per month. I know this works fine with GROUP BY month but only with months with orders in it. Now I want also the months with no orders so I get all months.

This is my query:

SELECT SUM(VerkoopfactBedrag) AS bedrag, DATE_FORMAT(VerkoopfactDatum,'%M') AS date 
FROM verkoopfacturen 
WHERE Verkoopfact_UserId = 12 
   AND VerkoopfactDatum BETWEEN '2011-01-01' AND '2011-12-30' 
GROUP BY MONTH(VerkoopfactDatum)

So when the result of a month is 0 I want to see the month with value 0 but now the month don't show up.

Is this possible?

解决方案

One way to do this is to create and populate a table full of consecutive months.

You can then OUTER JOIN using that table.

So something like:

drop table if exists all_months;

create table all_months (a_month_id int unsigned PRIMARY KEY,a_month varchar(20) NOT NULL, UNIQUE KEY `all_months_uidx1` (a_month));

insert into all_months values (1,'January');
insert into all_months values (2,'February');
insert into all_months values (3,'March');
insert into all_months values (4,'April');
insert into all_months values (5,'May');
insert into all_months values (6,'June');
insert into all_months values (7,'July');
insert into all_months values (8,'August');
insert into all_months values (9,'September');
insert into all_months values (10,'October');
insert into all_months values (11,'November');
insert into all_months values (12,'December');

SELECT SUM(IFNULL(t1.VerkoopfactBedrag,0)) AS bedrag, 
am.a_month AS date
from
(
select
ifnull(vn.VerkoopfactBedrag,0) as VerkoopfactBedrag,
cast(DATE_FORMAT(VerkoopfactDatum, '%M') as char) as mdate
FROM verkoopfacturen vn
WHERE Verkoopfact_UserId = 12 
AND VerkoopfactDatum BETWEEN '2011-01-01' AND '2011-12-31' 
GROUP BY DATE_FORMAT(VerkoopfactDatum, '%M')
) t1 RIGHT OUTER JOIN all_months am on t1.mdate = am.a_month
group by am.a_month
order by a_month_id asc;

PS Not sure if you have anything against Oudejaarsavond but there are 31 days in December ;-)

这篇关于按月包括空月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！