Counting distinct types in one query
Posted: Wed Feb 16, 2011 10:07 am
I have a table which I am joining (LEFT) with another table, simple right? In plain English, I have one table which holds work orders, each work order table can have none or one or more reworks. Reworks can be broken into two distinct types:
1. Additionals
2. Reprocesses
I am joining these two tables as one but only returning the DISTINCT work orders, preferably with calculated fields at the end of each indicating how many additionals and reprocesses are in each work order.
Here is what I have thus far:
Obviously this OR'ing is causing both additional and reprocess to be included in the sum thus yielding incorrect results. What I need to do (as a last resort) is two create two views, one which counts the additionals along with work orders, another to count the reprocesses along with work orders. Ideally this can be done in a single query WITHOUT using sub-queries (sub-selects are not allowed in VIEWS in the version of MySQL I am using anyways).
Any ideas?
Cheers,
Alex
1. Additionals
2. Reprocesses
I am joining these two tables as one but only returning the DISTINCT work orders, preferably with calculated fields at the end of each indicating how many additionals and reprocesses are in each work order.
Here is what I have thus far:
Code: Select all
SELECT
*,
rw.id_defect,
rw.id_type,
rw.id_user,
rw.title,
rw.comment,
COUNT(rw.id_primary) AS rework_additionals,
COUNT(rw.id_primary) AS rework_reprocesses
FROM
erp_workorder_search AS wo
LEFT JOIN
erp_rework AS rw
ON
wo.id_primary = rw.id_workorder
WHERE
rw.id_type = 1 # Additional
OR
rw.id_type = 2 # ReprocessAny ideas?
Cheers,
Alex