Include NULL as 0 in COUNT SQL Query

Question

I know for a fact this has been asked a few times before, but none of the answered questions relating to this seem to work or are far too confusing for me..

I should probably explain.

I'm trying to create an AJAX script to run to order some results by the number of 'Likes' it has.

My current code is this:

SELECT COUNT(*) AS total, likes.palette_id, palette.*
FROM likes LEFT JOIN palette ON likes.palette_id = palette.palette_id

GROUP BY likes.palette_id 
ORDER BY total DESC

Which works fine, however it doesn't list the results with 0 likes for obvious reasons, they don't exist in the table.

I've attached images of the current tables:

Likes table: https://i.sstatic.net/kY6UX.jpg

Palette table: https://i.sstatic.net/g99Cp.jpg

There are no results in the likes table until the user clicks 'Like'. It is then that the database gets updated and the palette_id and user_id are inserted.

I'm trying to count how many times *palette_id* occurs in the likes table but also display 0 for all palettes that don't appear in the likes table.

Is this possible? If so, can someone help me out at all?

Thank you

zerkms · Accepted Answer · 2013-10-25 02:21:44Z

1

It might not be the exact MySQL syntax (I'm used to SQL Server), but should be pretty straight forward to translate if needed.

SELECT p.*, IFNULL(l.total, 0) AS total
FROM palette p
LEFT JOIN (
    SELECT palette_id, COUNT(*) AS total
    FROM likes
    GROUP BY palette_id
) l
ON l.palette_id = p.palette_id
ORDER BY total

edited Oct 25, 2013 at 2:21

zerkms

256k73 gold badges447 silver badges551 bronze badges

answered Oct 25, 2013 at 2:01

plalx

43.8k7 gold badges77 silver badges94 bronze badges

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

5 Comments

gromey Over a year ago

I understand what you've put, but I think something may be missing? I'm getting a syntax error around the (l.total, 0)...

zerkms Over a year ago

@DavidPottrell: do we need to guess or you provide the exact error message?

gromey Over a year ago

My apologies, It just looked like a typical syntax error but I may be wrong! #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 0) AS total FROM palette p LEFT JOIN ( SELECT palette_id, COUNT(*) AS total' at line 1

zerkms Over a year ago

@DavidPottrel: sheck plalx's answer now (ISNULL -> IFNULL)

plalx Over a year ago

@zerkms No worries, I'm actually glad you made it better! ;)

Esteban Elverdin · Accepted Answer · 2013-10-25 02:58:43Z

1

Try this:

SELECT COUNT(likes.palette_id) AS total, palette.palette_id, palette.*
FROM palette LEFT JOIN likes ON likes.palette_id = palette.palette_id
GROUP BY palette.palette_id 
ORDER BY total DESC

EDIT:

In regards to the discussion about listing columns that are not in the GROUP BY, there's a good explanation in this MySql documentation page.

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

In this example, the palette information not added to the GROUP BY will be the same for each group because we are grouping by palette_id so there won't be any issue using palette.*

edited Oct 25, 2013 at 2:58

answered Oct 25, 2013 at 2:00

Esteban Elverdin

3,5821 gold badge20 silver badges21 bronze badges

13 Comments

plalx Over a year ago

Is it normal that you can select more fields that those listed in the group by statement? That's incorrect in MSSQL.

zerkms Over a year ago

@plalx: mysql forgives you some stupid things :-) PS: though it is possible to set it up to respect some strict requirements

Esteban Elverdin Over a year ago

@plalx, you are right, I just focused on the join part. thanks for noticing that

plalx Over a year ago

@zerkms Yeah that's what I tought, because it doesn't make any sense to me.

gromey Over a year ago

@Esteban-Everdin: This works perfectly! So is there a smarter way of getting all results from the palette table along with the count? Thanks though!

|

Michael - sqlbot · Accepted Answer · 2013-10-25 03:01:20Z

0

Your join is written backwards. It should be palette LEFT JOIN likes, because you want all rows in palette and rows in likes, if they exist. The "all rows in palette" will get you a palette_id for the entries there without any matching "likes."

answered Oct 25, 2013 at 3:01

Michael - sqlbot

180k28 gold badges388 silver badges458 bronze badges

Collectives™ on Stack Overflow

Include NULL as 0 in COUNT SQL Query

3 Answers 3

5 Comments

13 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

13 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related