CODEWITHSUNDEEP
sqloracle11ggroup-bygroup-summaries
user2360190
user2360190

Reputation: 1

Oracle SQL: retrieve sum and value from row in group based on value of another column

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!

Upvotes: 0

Views: 1226

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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.

Upvotes: 0

Related Questions