November 5, 2007 at 1:12 pm
How can I adjust the following query to give me a TOTAL rating grouped for each INSPNO. A few entries have a value for ratingkey 1000 AND ratingkey 1001 (see INSPNO 14455), but I need them combined into one line. Could someone please point me in the right direction?
Thanks,
QUERY-
select (c.unitid+'*'+c.unitid2) Main, it.inspno, ir.inspkey, ir.ratingkey, sum(rating) rating
from
comp c
join insmnft it on c.compkey = it.compkey
join insmnfr ir on ir.inspkey = it.inspkey
group by (c.unitid+'*'+c.unitid2), it.inspno, ir.inspkey, ir.ratingkey, ir.rating
having ir.ratingkey 0.0
order by it.inspno
RESULT -
Main inspnoinspkeyratingkeyrating
JJ2703MH025*JJ2703MH011 144501446110005.0
JJ2703MH030*JJ2702MH019 144531446410013.0
JJ2703MH031*JJ2703MH030 144541446510013.0
JJ2703MH032*JJ2703MH031 144551446610002.0
JJ2703MH032*JJ2703MH031 144551446610013.0
JJ2703MH033*JJ2703MH031 144561446710013.0
JJ2703MH037*JJ2703MH051 144601447110012.0
JJ2703MH042*JJ2703MH041 144651447610015.0
JJ2705MH062*JJ2705MH067 145561456710015.0
JJ2803MH010*JJ2803MH009 145741458510011.0
JJ2803MH020*JJ2803MH021 145781458910013.0
JJ2803MH033*JJ2803MH032 1458314594100115.0
November 5, 2007 at 2:37 pm
The easiest thing to do would be to take the ir.ratingkey out of the query. Then, you would get a sum for all ratingkeys for an inspno. The only other thing that you could do would be to replace the ir.ratingkey column with a formula that combines appropriate values together into a single value for grouping. An example could be substring(ir.ratingkey,1,3) for the first three digits.
Hope this helps.
November 5, 2007 at 3:21 pm
Thanks for your response, Rich -
My problem is that if a subquery is required to group the totals, I can't figure out where it goes or how to write it properly. Nothing I have tried has worked. If you could be more explicit, I'd really appreciate it.
In my sample there are two rating keys - 1000 and 1001. I have already excluded 1002, although it did not print in my original copy so I'll copy the query again here.
select (c.unitid+'*'+c.unitid2) Main, it.inspno, ir.inspkey, sum(rating) rating
from
comp c
join insmnft it on c.compkey = it.compkey
join insmnfr ir on ir.inspkey = it.inspkey
group by (c.unitid+'*'+c.unitid2), it.inspno, ir.inspkey, ir.ratingkey, ir.rating
having ir.ratingkey in (1000, 1001) and ir.rating>0.0
order by it.inspno
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply