Need help with summary column

  • 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

  • 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.

  • 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