MySQL Return Rows With Zero Count

Question

Table layouts

reject_data

+-----------+-----------------+------------------+---------------+
| reject_id | reject_location | reject_equipment | reject_time   |
+-----------+-----------------+------------------+---------------+
| 1         | 7               | 6                | 1326795921000 |
+-----------+-----------------+------------------+---------------+
| 2         | 7               | 1                | 1326796641000 |
+-----------+-----------------+------------------+---------------+
| 3         | 7               | 6                | 1326799521000 |
+-----------+-----------------+------------------+---------------+
| 4         | 6               | 5                | 1326800781000 |
+-----------+-----------------+------------------+---------------+
| 5         | 7               | 3                | 1326802281000 |
+-----------+-----------------+------------------+---------------+
| 6         | 7               | 4                | 1326802941000 |
+-----------+-----------------+------------------+---------------+
| 7         | 7               | 1                | 1326814161000 |
+-----------+-----------------+------------------+---------------+
| 8         | 6               | 2                | 1328026700000 |
+-----------+-----------------+------------------+---------------+

equipment

+--------------+------------------+
| equipment_id | equipment_string |
+--------------+------------------+
| 1            | Microdoser       |
+--------------+------------------+
| 2            | Monoblock        |
+--------------+------------------+
| 3            | Valve Magnet     |
+--------------+------------------+
| 4            | Checkweigher     |
+--------------+------------------+
| 5            | Microleak        |
+--------------+------------------+
| 6            | Capper           |
+--------------+------------------+

Query

SELECT equipment_string AS eqpt, COUNT(reject_id) AS cnt
FROM reject_data
RIGHT OUTER JOIN equipment ON (reject_equipment = equipment_id) 
WHERE reject_location IN (1,2,7) AND equipment_id IN (1,2,3,4,5,6) AND reject_time BETWEEN 1329479810000 AND 1329483410000
GROUP BY equipment_id
ORDER BY cnt DESC

Question

How do I alter my query so that it also returns equipment that have zero counts? Really stuck :(

Thank you for any help.

Note: Dynamic variable populates the IN normally.

Simon at The Access Group · Accepted Answer · 2012-02-17 15:14:02Z

2

SELECT 
    e.equipment_string AS eqpt, 
    COUNT(reject_id) AS cnt

FROM equipment AS e

LEFT JOIN reject_data AS rd
    ON rd.reject_equipment = e.equipment_id
    AND rd.reject_location IN (1,2,7)
    AND rd.reject_time BETWEEN 1329479810000 AND 1329483410000

WHERE e.equipment_id IN (1,2,3,4,5,6)

GROUP BY e.equipment_id
ORDER BY cnt DESC

By limiting the join criteria you decrease the amount of rows MySQL needs to retrieve

edited Feb 17, 2012 at 15:14

answered Feb 17, 2012 at 15:04

Simon at The Access Group

7,0251 gold badge25 silver badges47 bronze badges

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

2 Comments

ruakh Over a year ago

-1, sorry: your first query is equivalent to the OP's query, and will have the same problem.

Simon at The Access Group Over a year ago

I preferred the second query anyhow - have removed the first

ruakh · Accepted Answer · 2012-02-17 15:05:12Z

1

The WHERE clause does the filtering after the JOIN, so it will filter out any records where the JOIN failed (since reject_location IN (1,2,7) won't be true for those). You need to move those restrictions to the ON clause:

SELECT equipment_string AS eqpt, COUNT(reject_id) AS cnt
FROM reject_data
RIGHT OUTER JOIN equipment
       ON reject_equipment = equipment_id
      AND reject_location IN (1,2,7)
      AND reject_time BETWEEN 1329479810000 AND 1329483410000
WHERE equipment_id IN (1,2,3,4,5,6)
GROUP BY equipment_id
ORDER BY cnt DESC

In this case, another approach, which you might find more clear, is to use a subquery rather than a join:

SELECT equipment_string AS eqpt,
       ( SELECT COUNT(1)
           FROM reject_data
          WHERE reject_equipment = equipment_id
            AND reject_location IN (1,2,7)
            AND reject_time BETWEEN 1329479810000 AND 1329483410000
       ) AS cnt
  FROM equipment
 WHERE equipment_id IN (1,2,3,4,5,6)
 ORDER BY cnt DESC

answered Feb 17, 2012 at 15:05

ruakh

185k29 gold badges292 silver badges324 bronze badges

4 Comments

Kaii Over a year ago

when i understand the OP correctly, those filters are desired behaviour.

ruakh Over a year ago

@Kaii: Sorry, but you didn't understand the OP correctly. He/she wants to "also return[] equipment that have zero counts".

Kaii Over a year ago

yes, return all rows from equipment, even when there are no countable rejects in table reject_data..

ruakh Over a year ago

@Kaii: Yes, so those filters aren't desired behavior.

Collectives™ on Stack Overflow

MySQL Return Rows With Zero Count

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related