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'?
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments