mySQL - how to keep count() function from dropping 0 counts?

Question

I'm new to mySQL and working on a query which essentially takes one initial table and summarizes its contents in two separate tables. The second "summary" table is dropping tuples where a value is 0; I need to fix that. Here's the specifics:

I have a large Inventory table, which I assemble from other tables:

SID  CID   SECTION  RATING
==========================
100  1001  1        A
101  1001  1        A
102  1002  1        B
103  1002  2        F
104  1003  1        A
...etc...

("CID" and "Section" are the primary keys here, I don't know if whether the PK is spread across two attributes is significant here.) I have a successful query ("numTable1") which counts the number of SIDs for each CID and Section. For example, with the above Inventory table, "sumTable1" would be:

CID   SECTION  numSID
=====================
1001  1        2
1002  1        1
1002  2        1
1003  1        1
...etc...

Here's the SQL:

create temporary table numTable1(
    SELECT ot1.cid, ot2.section, count(ot1.sid) 'numSID'
    FROM otherTable1 ot1, otherTable2 ot2
    WHERE ot1.cid=ot2.cid and ot1.section=ot2.section
    GROUP BY ot1.cid, ot2.section
);

"NumTable" works great. Here's the problem: I also need a query which counts the number of 'A' Ratings per CID/Section:

create temporary table numA(
    select t9.cid, t9.section, count(ot1.rating) 'As'
    from otherTable1 ot1, t9
    where grade = 'A' and t9.cid=ot1.cid and t9.section=ot1.section
    group by t9.cid, t9.section

);

Here's the output of "numA":

CID   SECTION  As
=====================
1001  1        2
1003  1        1
...etc...

But here's the problem; some CID/Sections have no A Ratings, and they are being washed out. What I need is this:

CID   SECTION  As
=====================
1001  1        2
1002  1        0
1002  2        0
1003  1        1
...etc...

You'll note that the above table can be seamlessly merged with "numTable1", which is the ultimate aim here:

CID   SECTION  numSID   As
==========================
1001  1        2        2
1002  1        1        0
1002  2        1        0
1003  1        1        1

But as long as "numA" drops CID/Sections with 0 As, I'm dead in the water. I assume my "group by" statement in "numA" is washing out the zeroes... but I'm not sure about that. Any suggestions?

is not clear to me you want remove the rows with as = 0 form the last table ? — ScaisEdge, Aug 03 '17 at 16:51
You have to join back your counts to the base data to enrich it with the 0 count records. — Norbert, Aug 03 '17 at 16:53
@scaisEdge thanks for the clarification... No, I need the rows with 0s. The problem with the "numA" query is that it is dropping the 0 rows... I need to fix that. — Pete, Aug 03 '17 at 17:47
@NigelRen -- Thanks, that's a great idea. When I do the left join, I get the "0 rows" that were being dropped, but the values of "Aa" is NULL, not 0. Is there some syntax tweak which can swap NULLs for 0s? — Pete, Aug 03 '17 at 18:30
@NigelRen - oh wait: https://stackoverflow.com/questions/16840522/replacing-null-with-0-in-a-sql-server-query#16840585 Duh...! — Pete, Aug 03 '17 at 18:31

score 0 · Answer 1 · answered Aug 04 '17 at 14:18

@NigelRen provided the solution - a left join. Here's the code:

-- Left Join on numTable1 with numA... makes for total students // numAs
-- Resultant table is totA: "Total A Ratings"
create temporary table totA(
    select numTable1.cid, numTable19.section, numTable1.numStudents, if(numA.As is null, 0, numA.As) 'A Ratings'
    from numTable1
    left join numA on numTable1.cid=numA.cid and numTable1.section=numA.section
);

mySQL - how to keep count() function from dropping 0 counts?

1 Answers1