Page 1 of 1

Counting distinct types in one query

Posted: Wed Feb 16, 2011 10:07 am
by alex.barylski
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:

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 # Reprocess
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

Re: Counting distinct types in one query

Posted: Wed Feb 16, 2011 10:20 am
by John Cartwright
Would a UNION count as subqueries?

Re: Counting distinct types in one query

Posted: Wed Feb 16, 2011 10:34 am
by alex.barylski
Thought about that, however then what happens is I get duplicate results, like:

Code: Select all

63663, RT-2323332, 2300-782783, PT SUPPORT, ACE AVIATION, Rolls Royce, 0, 3
63663, RT-2323332, 2300-782783, PT SUPPORT, ACE AVIATION, Rolls Royce, 1, 0
The same record is return twice, once for the count of additionals, another for a count of reprocesses. I need to then merge these tables so the resulting output is like:

Code: Select all

63663, RT-2323332, 2300-782783, PT SUPPORT, ACE AVIATION, Rolls Royce, 1, 3
If that makes any sense?

Cheers,
Alex

Re: Counting distinct types in one query

Posted: Wed Feb 16, 2011 10:54 am
by John Cartwright
Yea, but you can apply additional selection / filtering on the results of your union.

Code: Select all

SELECT SUM(your_first_count_column), SUM(your_second_count_column) 
FROM
(
  //query 1
) UNION (
  // query 2
)
GROUP BY your_grouping_columns

Re: Counting distinct types in one query

Posted: Wed Feb 16, 2011 11:48 am
by Weirdan
summing booleans to the rescue:

Code: Select all

SELECT 
  wo.*,
 
  sum(rw.id_type = 1) AS rework_additionals,
  sum(rw.id_type = 2) AS rework_reprocesses
FROM 
  erp_workorder_search AS wo
LEFT JOIN
  erp_rework AS rw
ON
  wo.id_primary = rw.id_workorder
  and (
     rw.id_type = 1 # Additional
     OR  rw.id_type = 2 # Reprocess
  )
group by wo.id_primary
I also moved type limit into join condition so that it won't filter out orders without any additionals or reprocesses but properly report them as having 0, 0 counts

rw.* fields were removed because they don't make any sense in an aggregation scenario (with 'group by' involved) and you didn't say you actually needed them.

Re: Counting distinct types in one query

Posted: Thu Feb 17, 2011 7:45 am
by alex.barylski
Here is my query, I applied your changes and they seem to work and look very promising but I am missing osmething:

[sql]SELECT
wo.*,
#rd.username AS rework_user,
#rd.username AS rework_station,
#rd.description AS rework_defect,
rw.comment AS rework_comment,
rw.id_type AS rework_type,

SUM(rw.id_type = 1) AS rework_additionals,
SUM(rw.id_type = 2) AS rework_reprocesses
FROM
erp_workorder_search AS wo
RIGHT JOIN
erp_rework AS rw
ON
wo.id_primary = rw.id_workorder
LEFT JOIN
list_rework_defect AS rd
ON
rw.id_defect = rd.id_primary
AND
(
rw.id_type = 1 # Additional
OR
rw.id_type = 2 # Reprocess
)
GROUP BY
wo.id_primary[/sql]

The problem is, while the SUM appears to be correct as the result set looks like:

Code: Select all

67020,1,1,1,1,22,1,1297787021,1297787021,0,0,0,0,1,27,3010D,,NSN,C30,23030976,IMPELLER,,Rolls Royce,Work In Progress,DAVIS MACHINE,Receiving,gdfgdfgdfg,1,1,2
Observe the last two fields, ONE additional and TWO reprocesses, this is accurate but why am I not getting all three in the result set???

Cheers,
Alex

Re: Counting distinct types in one query

Posted: Thu Feb 17, 2011 9:42 am
by Weirdan
alex.barylski wrote: The problem is, while the SUM appears to be correct as the result set looks like:

Code: Select all

67020,1,1,1,1,22,1,1297787021,1297787021,0,0,0,0,1,27,3010D,,NSN,C30,23030976,IMPELLER,,Rolls Royce,Work In Progress,DAVIS MACHINE,Receiving,gdfgdfgdfg,1,1,2
Observe the last two fields, ONE additional and TWO reprocesses, this is accurate but why am I not getting all three in the result set???
and what would you like your resultset to look like? Something like this:

Code: Select all

67020,1,1,1,1,22,1,1297787021,1297787021,0,0,0,0,1,27,3010D,,NSN,C30,23030976,IMPELLER,,Rolls Royce,Work In Progress,DAVIS MACHINE,Receiving,Installed audio,1,1,2
67020,1,1,1,1,22,1,1297787021,1297787021,0,0,0,0,1,27,3010D,,NSN,C30,23030976,IMPELLER,,Rolls Royce,Work In Progress,DAVIS MACHINE,Receiving,Repainted to green,2,1,2
67020,1,1,1,1,22,1,1297787021,1297787021,0,0,0,0,1,27,3010D,,NSN,C30,23030976,IMPELLER,,Rolls Royce,Work In Progress,DAVIS MACHINE,Receiving,Repainted back to red - why couldn't this guy talk to his wife who likes red *before* asking us to paint it green?,2,1,2
?

Re: Counting distinct types in one query

Posted: Thu Feb 17, 2011 11:18 am
by alex.barylski
You had me kinda scared for a minute when I read your custom comments...I was like whoa thats not right haha...anyways.

Yes that is the prefered resultset so the QA dept. can import into excel and filter their hearts content.

I assume using aggregates this isn't possible? Not a game breaker just would have been kinda nice :)

Cheers,
Alex

Re: Counting distinct types in one query

Posted: Thu Feb 17, 2011 5:54 pm
by Weirdan
alex.barylski wrote:I assume using aggregates this isn't possible? Not a game breaker just would have been kinda nice :)
Well, it's possible with a subquery, just not very efficient and produces duplicate data (counts would be repeated for every workorder, thus preventing further aggregation on those columns). Something like this should work (if inner select produces lots of rows it would be awfully slow though, because join won't use any indexes):

Code: Select all

SELECT 
  wo.*,
  rw.comment AS rework_comment,
  rw.id_type AS rework_type,
  ifnull(counts.rework_additionals, 0) as rework_additionals,
  ifnull(counts.rework_reprocesses, 0) as rework_reprocesses
FROM 
  erp_workorder_search AS wo
left join (
  SELECT 
    rw.id_workorder,
    sum(rw.id_type = 1) AS rework_additionals,
    sum(rw.id_type = 2) AS rework_reprocesses
  FROM 
    erp_rework AS rw
  where
     rw.id_type = 1 # Additional
     OR  rw.id_type = 2 # Reprocess
  GROUP BY rw.id_primary
) counts
ON
  wo.id_primary = counts.id_workorder
GROUP BY 
  wo.id_primary

Re: Counting distinct types in one query

Posted: Fri Feb 18, 2011 7:36 am
by VladSun
Weirdan +1

Selecting columns/data that have no correlation between them just doesn't make sense.

Code: Select all

  *,
  rw.id_defect,
  rw.id_type,
  rw.id_user,

  rw.title,
  rw.comment, 
and

Code: Select all

  COUNT(...) AS rework_additionals,
  COUNT(...) AS rework_reprocesses

Re: Counting distinct types in one query

Posted: Fri Feb 18, 2011 9:04 am
by alex.barylski
Problem is (my version anyways if memory serves correct) MySQL VIEWS do not support sub-queries. :(

In anycase, I appreciate your help guys, unfortunately (for me) I had to go with a alternative solution because of other requirements just discovered. Thanks again :)

Cheers,
Alex