MySQL IFNULL not returning default value

Question

id    category    active
------------------------
1     1           1
2     3           0
3     3           1
4     3           1
5     1           0

I want to select total count of each category and count of active in each cateogry.

Since there's no category=2, I used IFNULL, but it didn't return 0

SELECT
    IFNULL(COUNT(id), 0) AS total,
    SUM(CASE WHEN active = 1 THEN 1 ELSE 0 END) AS active
FROM
    table
WHERE
    category IN (1,2,3)
GROUP BY
    category

The result returned 2 arrays, instead of 3 as expected

// result from category=1
Array
(
    [total] => 2
    [active] => 1
)
//  result from category=3
Array
(
    [total] => 3
    [active] => 2
)

How can I get category=2 into the result

//  result from category=2
Array
(
    [total] => 0
    [active] => 0
)

score 1 · Answer 1 · answered Nov 15 '12 at 09:39

1

Your query is returning the right result, just not the one you expected, COUNT is a grouping clause and only groups existing values, it won't return = to category=2 because this category doesn't exist.

answered Nov 15 '12 at 09:39

Euclides Mulémbwè

1,677
11
18

OK, I got it, COUNT will not return on NULL. Can I get a solution for what I'm expecting? – user1643156 Nov 15 '12 at 09:48
do you have a table of categories? does that table have category 2? – Euclides Mulémbwè Nov 15 '12 at 09:58
yes, I do have a category table. but for this specific question, I created a test db and only have this one table. so besides using join and manually validating the result (as I commented on bonCodigo), there's no other way to do it right? – user1643156 Nov 15 '12 at 10:02
I don't see a way of doing it without a left join. – Euclides Mulémbwè Nov 15 '12 at 12:38

score 0 · Answer 2 · answered Nov 15 '12 at 09:37

0

Count(*) will never return null

Try this:

SELECT
    COUNT(id) AS total,
    SUM(CASE WHEN active = 1 THEN 1 ELSE 0 END) AS active
FROM
    table
WHERE
    category IN (1,2,3)
GROUP BY
    category

answered Nov 15 '12 at 09:37

Sashi Kant

13,277
9
44
71

this query will also only return category 1 and 3, category 2 doesn't exist so it will not be on the result set. – Euclides Mulémbwè Nov 15 '12 at 09:41

Ja͢ck · Answer 3 · 2012-11-15T09:51:24.200

0

It's practically not possible to generate a row that contains a category that's not present in your table.

You should have another table that contains all categories and then use a LEFT JOIN like this:

SELECT categories.category, 
    COUNT(*) AS total,
    SUM(`table`.active)
FROM categories
LEFT JOIN `table` USING (category)
WHERE categories.category IN (1,2,3)
GROUP BY categories.category;

edited Nov 15 '12 at 09:51

answered Nov 15 '12 at 09:44

Ja͢ck

170,779
38
263
309

score 0 · Answer 4 · answered Nov 15 '12 at 09:49

Where database engine should take rows for id=2?

You need to select from categories table and then count from another.

As for your need query should looks something like this (I didn't check cause I don't have your tables and data in my DB)

SELECT
    c.id,
    COUNT(t.id) AS total,
    SUM(CASE WHEN t.active = 1 THEN 1 ELSE 0 END) AS active
FROM
    categories_table AS c
LEFT JOIN table AS t ON c.id=t.category
WHERE
    c.id IN (1,2,3)
GROUP BY
    t.category

This is exactly what I meant in the first part of my answer. Lucky you, Igor was nice to give u the code :-) — bonCodigo, Nov 15 '12 at 09:53

score 0 · Answer 5 · answered Nov 15 '12 at 09:50

0

How could you expect to have a count on an inexistent group ;-) UNLESS, you have a category PARENT table that you would want to join here to get what you want.

Otherwise, you may use extra clause to validate your arrays for unavailable categories and manually inject a code line to show them in the final output.

So when all categories available, happy days else, your validation does the job.

answered Nov 15 '12 at 09:50

bonCodigo

14,268
1
48
91

that's the way I'm doing it right now, SELECT also category in query. and (PHP) foreach...if(!array_key_exists())...$cat[2][total] = 0; $cat[2][active] = 0; just wondering if I could do it within one sql query. – user1643156 Nov 15 '12 at 09:59
In reality people do not intend to wrap things about objects in the space but your DB :D perhaps you may be able to figure out some idea on [this discussion](http://stackoverflow.com/questions/11161181/sql-row-sum-for-some-colums) however as number of departments increase it would be super klugey query.... – bonCodigo Nov 15 '12 at 10:25

MySQL IFNULL not returning default value

5 Answers5