MySQL - Select with COUNT returning a NULL row

Question

This works

SELECT DISTINCT  b.b_id 
FROM             b INNER JOIN c 
ON               b.b_id=c.b_id  
WHERE            c.active='yes' AND b.featured='no'

When the results should be 0 rows returned, this returns a null row with count = 0

SELECT DISTINCT  b.b_id, COUNT(c.c_id) AS count
FROM             b INNER JOIN c 
ON               b.b_id=c.b_id  
WHERE            c.active='yes' AND b.featured='no'

Am I doing something wrong?

When you use an aggregate function (COUNT), you need to GROUP BY the non-aggregated column(s). MySQL shouldn't even allow that query, but it does which leads to unpredictable behavior... — mechanical_meat
– mechanical_meat, Commented May 24, 2012 at 18:46
ahh, ok, so i've removed the DISTINCT and appended GROUP by b.b_id at the end of the query. is that right? — lewicki
– lewicki, Commented May 24, 2012 at 18:49

Michael Fredrickson · Accepted Answer · 2012-05-24 18:48:25Z

3

I think you want a left join instead of an inner join since you want to return a count of 0 instead of a missing row when there is no matching c record for a given b record.

Also, you should include a group by when using an aggregate function, such as count.

SELECT
    b.b_id,
    COUNT(DISTINCT c.c_id) AS count
FROM 
    b 
    LEFT JOIN c 
        ON b.b_id=c.b_id  
        AND c.active='yes' 
WHERE b.featured='no'
GROUP BY b.b_id

answered May 24, 2012 at 18:48

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

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

1 Comment

Barbz_YHOOL Over a year ago

the "group by" clause makes it possible to return a NULL value, thank you

Bohemian · Accepted Answer · 2012-05-24 18:49:23Z

1

Try putting DISTINCT inside COUNT():

SELECT b.b_id, COUNT(DISTINCT c.c_id) AS count
FROM b
JOIN c ON b.b_id=c.b_id  
WHERE c.active='yes' 
AND b.featured='no'
GROUP BY 1

btw, please consider formatting your SQL like this, which is more readable IMHO.

answered May 24, 2012 at 18:49

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Comments

VVLeon · Accepted Answer · 2012-05-24 18:58:54Z

0

I'd like to try GROUP BY instead of DISTINCT.

    SELECT b.b_id, COUNT(c.c_id) AS count
    FROM 
    b INNER JOIN c 
    ON 
    b.b_id=c.b_id  
    WHERE 
    c.active='yes' 
    AND 
    b.featured='no'
    GROUP BY b.b_id

answered May 24, 2012 at 18:58

VVLeon

3993 silver badges8 bronze badges

Collectives™ on Stack Overflow

MySQL - Select with COUNT returning a NULL row

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related