How do I get rid of these subqueries?
I have this request:
select distinct pdi.height, pdi.width, pj.jobnum
, (select count(barcode) from productiondoorinformation
inner join view_productionjobs on view_productionjobs.ctr=productiondoorinformation.productionjobsctr
where view_productionjobs.jobnum=pj.jobnum
and productiondoorinformation.height=pdi.height
and productiondoorinformation.width=pdi.width
and productiondoorinformation.alternaterating='PARTICLE') as particleqty
, (select count(barcode) from productiondoorinformation
inner join view_productionjobs on view_productionjobs.ctr=productiondoorinformation.productionjobsctr
where view_productionjobs.jobnum=pj.jobnum
and productiondoorinformation.height=pdi.height
and productiondoorinformation.width=pdi.width
and productiondoorinformation.alternaterating<>'PARTICLE') as laminatedqty
from productiondoorinformation pdi inner join view_productionjobs pj on pj.ctr=pdi.productionjobsctr
where pj.jobnum='' + @Jobnum + ''
There must be a better way to be the best way to do it. I hate subheadings, but I'm not sure enough to rewrite it without them. There are two different counts that count, which you will notice when you see the where clauses for each.
This request takes nine seconds. This is too long. Any other query I write against these tables returns immediately. Any suggestions? I've tried a group, etc., but can get it to work either way (particle or particle), but not both.
The returned data should look like this:
height | width | jobnum | particleqty | laminatedqty
79 49 t1000 10 5
78 49 t1000 1 3
79 47 t1000 15 0
source to share
You are on the right track. You should use SUM
instead of COUNT
and GROUP BY
:
select pdi.height, pdi.width, pj.jobnum,
SUM(CASE pdi.alternaterating WHEN 'PARTICLE' THEN 1 ELSE 0 END) particleqty,
SUM(CASE pdi.alternaterating WHEN 'PARTICLE' THEN 0 ELSE 1 END) laminatedqty
from productiondoorinformation pdi
inner join view_productionjobs pj on pj.ctr=pdi.productionjobsctr
where pj.jobnum='' + @Jobnum + ''
group by pdi.height, pdi.width, pj.jobnum
BTW, not sure if you want those single ( ''
) quotes around @Jobnum ...
source to share