CODEWITHSUNDEEP
sqlgroup-bycount
Bauerhof
Bauerhof

Reputation: 165

select the count of records that don't matches those in the right table

suppose we have the following the error_description table:

+----------+-------------+
| error_id | error_token |
+----------+-------------+
| 1        | error_1     |
+----------+-------------+
| 2        | error_2     |
+----------+-------------+
| 3        | error_3     |
+----------+-------------+
| 4        | error_4     |
+----------+-------------+
| 5        | error_5     |
+----------+-------------+
| 6        | error_6     |
+----------+-------------+

and the orders_error table:

+----------+----------+
| order_id | error_id |
+----------+----------+
| 1        | 1        |
+----------+----------+
| 2        | 1        |
+----------+----------+
| 3        | 2        |
+----------+----------+
| 4        | 3        |
+----------+----------+
| 5        | 1        |
+----------+----------+
| 6        | 1        |
+----------+----------+
| 7        | 2        |
+----------+----------+
| 8        | 2        |
+----------+----------+
| 9        | 3        |
+----------+----------+
| 10       | 4        |
+----------+----------+

it's requested to get the total number of occurrences for each error including those that never happened. the result supposed to be like:

+-------------+-------------+
| error_token | total_count |
+-------------+-------------+
| error_1     | 4           |
+-------------+-------------+
| error_2     | 3           |
+-------------+-------------+
| error_3     | 2           |
+-------------+-------------+
| error_4     | 1           |
+-------------+-------------+
| error_5     | 0           |
+-------------+-------------+
| error_6     | 0           |
+-------------+-------------+

i am useing a query like:

SELECT err.token,
       COUNT( ord.order_id )
  FROM       error_description err
  LEFT JOIN  order_error ord ON ( err.error_id = ord.error_id )
 GROUP BY err.token          
;

but i got only the occurrences of the errors that happened only (error_1 till error_4).

I need to include those errors that never happened?

Upvotes: 0

Views: 51

Answers (1)

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

Updated the column names as per your table definition.

SELECT err.error_token,
       COUNT( ord.order_id ) AS total_count 
FROM  error_description err
LEFT JOIN  order_error ord ON err.error_id = ord.error_id
GROUP BY err.error_token

Upvotes: 1

Related Questions