MYSQL count nulls;

Question

I have a table with items and the date that they're added. What I want is to get a report of added items by year and month.

My query:

SELECT COUNT(*) FROM items 
GROUP BY YEAR(FROM_UNIXTIME(items.added)), MONTH(FROM_UNIXTIME(items.added))

The query returns

+----------+
| COUNT(*) |
+----------+
|       45 |
|       22 |
|        8 |
|       12 |
|       27 |
+----------+

which is correct data, but i also want to get 0 from the months when there is no item added for the last 3 years. And also get the column name as YEAR - MONTH.

I tried SELECT IF(COUNT(*) IS NULL, 0, COUNT(*)) and also SELECT IFNULL(COUNT(*), 0) but none of them worked. I'm stuck.

You need a list of all months, if they are not in your table. A calendar table would be a typical approach. — Gordon Linoff
– Gordon Linoff, Commented Jul 16, 2018 at 12:10

Michał Turczyn · Accepted Answer · 2018-07-16 12:27:35Z

1

Try this:

--creation of calendar table
create table months(month int);
insert into months values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12);
create table years(year int);
insert into years values (2017),(2018); --here you specify for what years you want to have summary
create table calendarTable(month int, year int);
insert into calendarTable
select month, year from months cross join years;

select ct.year, ct.month, sum(case when i.added is null then 0 else 1) from calendarTable ct
left join items i on ct.year = YEAR(FROM_UNIXTIME(i.added)) and ct.month = MONTH(FROM_UNIXTIME(i.added))
group by ct.year, ct.month

answered Jul 16, 2018 at 12:27

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ramprasath .n · Accepted Answer · 2018-07-16 12:35:09Z

1

This will work for your condition in mysql workbench.

Select ifnull(count(*),0) from [TABLE NAME]

answered Jul 16, 2018 at 12:35

ramprasath .n

1005 bronze badges

Collectives™ on Stack Overflow

MYSQL count nulls;

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related