CODEWITHSUNDEEP
mysqlsql
john doe
john doe

Reputation: 161

MYSQL count nulls;

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.

Upvotes: 0

Views: 51

Answers (2)

ramprasath .n
ramprasath .n

Reputation: 100

This will work for your condition in mysql workbench.

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

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37337

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

Upvotes: 1

Related Questions