MySQL query with COUNT and join column from another table

Question

I have 2 tables with multiple fields.

Table1:

+--------+---+---------------+                                                  
| month  | id| VERDICT_id    |                                                  
+--------+------+------------+                                                  
| 201307 | 1 |             1 |                                                  
| 201307 | 2 |             4 |                                                  
| 201307 | 3 |             2 |                                                  
| 201307 | 4 |             2 |                                                  
| 201307 | 5 |          NULL |                                                  
| 201307 | 6 |             1 |                                                  
+--------+------+------------+

Like this for every new 'month', for each unique 'id' the 'VERDICT_ID' is set according to the value in Table2.

Table2:

+----+----------------------------------+
| id | title                            |
+----+----------------------------------+
|  1 | Passed                           |
|  2 | Failed (Component Fault)         |
|  3 | Failed (User Fault)              |
|  4 | Failed (Hardware Issue)          |
+----+----------------------------------+

I make a query which gives below output

Actual Output:

+--------+------------+----------+
| month  | VERDICT_id | COUNT(*) |
+--------+------------+----------+
| 201307 |          1 |        2 |
| 201307 |          2 |        2 |
| 201307 |          4 |        1 |
+--------+------------+----------+

What I want is,

+--------+------------+----------+
| month  | VERDICT_id | COUNT(*) |
+--------+------------+----------+
| 201307 |          1 |        2 |
| 201307 |          2 |        2 |
| 201307 |          3 |        0 |
| 201307 |          4 |        1 |
+--------+------------+----------+

The difference between these 2 output is, if any VERDICT_id doesn't exist for a month, then I want to print the VERDICT_id and COUNT as '0'.

But with the below query it's not possible.

select month,VERDICT_id, COUNT(*) FROM Table1 
where (month = 201307) AND (VERDICT_id BETWEEN 1 AND 4) GROUP BY month, VERDICT_id;

Q. Is this possible to make query to print the non existing VERDICT_id and COUNT as '0'?

You need to do a left join | possible duplicate of How to show rows with zero counts in mySQL group by query? — Paulina
– Paulina, Commented Sep 27, 2013 at 15:08

rs. · Accepted Answer · 2013-09-27 20:21:59Z

2

try this

SELECT v.*, count(t.verdict_id) as cnt FROM 
(
      SELECT month, id as verdict_id 
      from 
          (SELECT DISTINCT month FROM Table1 where (month = 201307)) M , 
          verdictTable
) v
LEFT OUTER JOIN Table1 t ON v.verdict_id = t.verdict_id and v.month = t.month
GROUP BY v.verdict_id, v.month

edited Sep 27, 2013 at 20:21

answered Sep 27, 2013 at 15:15

rs.

27.6k13 gold badges71 silver badges93 bronze badges

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

2 Comments

user645280 Over a year ago

I think you meant count(t.id). count(*) will include rows with no entry in table1.

himansusn Over a year ago

Perfect!!! It's working as I wanted... Thanks for quick help... I spent whole day trying to get a solution like this...

sunysen · Accepted Answer · 2013-09-27 15:14:44Z

0

try

select month,VERDICT_id, COUNT(*) as num FROM Table1 
where (month = 201307) AND (VERDICT_id BETWEEN 1 AND 4) 
GROUP BY month, VERDICT_id having num > 0;

answered Sep 27, 2013 at 15:14

sunysen

2,3711 gold badge15 silver badges13 bronze badges

Comments

AgRizzo · Accepted Answer · 2013-09-27 17:36:07Z

0

You need to force the month and VERDICT_id fields when the record does not exist. (The month is forced with the DISTINCT LIST. The VERDICT_id is forced with a JOIN without conditions to Table2)

SELECT LIST.`month` as `month`, Table2.id AS VERDICT_id, COUNT(Detail.id)
FROM 
  (SELECT DISTINCT `month`
  FROM Table1
  WHERE Table1.`month` = 201307) AS LIST
JOIN Table2
LEFT JOIN Table1 AS Detail
  ON LIST.`month` = Detail.`month`
    AND Table2.id = Detail.VERDICT_id
GROUP BY `month`, VERDICT_id
ORDER BY `month`, VERDICT_id;

edited Sep 27, 2013 at 17:36

answered Sep 27, 2013 at 15:20

AgRizzo

5,2711 gold badge15 silver badges29 bronze badges

1 Comment

himansusn Over a year ago

The solution works but not consistent. For some of the months none of the row is updated with VERDICT_id and it's set as NULL. In that case this query prints only VERDICT_id as "1" and COUNT(*) as "0".

Collectives™ on Stack Overflow

MySQL query with COUNT and join column from another table

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related