CODEWITHSUNDEEP
mysqlgroup-by
Leon van der Veen
Leon van der Veen

Reputation: 1672

Group by month including empty months

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?

Upvotes: 6

Views: 7180

Answers (2)

azhar
azhar

Reputation: 1

After searching for a simple solution i finally found this which I think is SIMPLE. This will show last year and this years sales side by side.

select 
       date_format(current_date - INTERVAL 1 YEAR,'%Y') as LAST_YR,
       date_format(NOW(),'%Y') as THIS_YR,
       monthname(date) as month,
       sum(case when year(date) = date_format(current_date - INTERVAL 1 YEAR,'%Y') then amount else 0 end) as sales_ly,
       sum(case when year(date) = DATE_FORMAT(NOW(),'%Y') then amount else 0 end) as sales_ty
from tablename
where date between date_format(current_date - INTERVAL 1 YEAR,'%Y-%01-%01')
and date_format(current_date, '%Y-%12-%31')
group by monthname(date)
order by max(month(date));

Upvotes: 0

Tom Mac
Tom Mac

Reputation: 9853

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 ;-)

Upvotes: 6

Related Questions