0

I am trying to create a summary query that returns the sum of the quantity for each group along with the description from from the row with the largest quantity in that group.

For example, if the table looks like this:

GROUP  QTY   DESC
-----  ---   ----
1      23    CCC
1      42    AAA
1      61    BBB

2      11    ZZZ
2      53    XXX
2      32    YYY

The query would return:

1     125    BBB   (desc from row with largest qty for group 1)
2      95    XXX   (desc from row with largest qty for group 2)

Thanks!

user2360190
  • 1
  • 1

1 Answers1

0

The window function row_number() is your friend for this type of query. It assigns a sequential number to values. You can then use this information in an aggregation:

select group, sum(qty), max(case when seqnum = 1 then desc end)
from (select t.*,
             row_number() over (partition by group order by qty desc) as seqnum
      from t
     ) t
group by group

By the way, group and desc are lousy names for columns because they conflict with reserved words. You should rename them or enclose them in double quotes in the query.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786