Hack Forums
[Help] SQL - How to count rows including ones that are blank? - Printable Version

+- Hack Forums (https://hackforums.net)
+-- Forum: Tech (https://hackforums.net/forumdisplay.php?fid=444)
+--- Forum: Coding (https://hackforums.net/forumdisplay.php?fid=151)
+---- Forum: Coders Lounge (https://hackforums.net/forumdisplay.php?fid=5)
+---- Thread: [Help] SQL - How to count rows including ones that are blank? (/showthread.php?tid=5423069)

SQL - How to count rows including ones that are blank? - Conundrum Account - Oct 3, 2016

For example you have a relational database with students and homework submissions. You're to calculate how many students have 10 or less homework entries. There should be 54 entries, but it's only coming up with 52 (that's because 2 students have not made any homework submissions ever).

I hope I've made sense. Does anyone know how to fix this so that it also counts blanks too?

Thanks

* - Neeeeeeeeeel.- - Oct 3, 2016

Something like:

Code
SELECT s.name, COUNT(*) AS he_count FROM students AS s LEFT JOIN homework_entries AS he ON (s.id = he.sid) GROUP BY he.sid HAVING he_count <= 10

It should output any student that has sent 10 entries or less. The ones who haven't ever sent any, should have "0" in the second column.

* - Conundrum Account - Oct 4, 2016

(Oct 3, 2016 07:07 AM)Neeeeeeeeeel.- Wrote:  Something like:

Code
SELECT s.name, COUNT(*) AS he_count FROM students AS s LEFT JOIN homework_entries AS he ON (s.id = he.sid) GROUP BY he.sid HAVING he_count <= 10

It should output any student that has sent 10 entries or less. The ones who haven't ever sent any, should have "0" in the second column.

Thanks a lot for your response it helped me get the correct answer. If you don't mind I've posted another question on this board which I'm unable to link because I only have 14 post count right now.

Thanks.