OraFAQ Forum: SQL & PL/SQL » Grouping with sum and retain first row

Home » SQL & PL/SQL » SQL & PL/SQL » Grouping with sum and retain first row (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Grouping with sum and retain first row [message #645337]

Thu, 03 December 2015 13:50

bamboo123
Messages: 1
Registered: December 2015

Junior Member

Hi,

I have this table:

COMPANY LOT GROUP_CODE LOCATION HOURS
ab1 1 xt mars 2
ab2 2 xt mars 3
ab3 3 xt mars 4
db1 1 ls earth 1
db2 2 ls earth 1
db3 3 ls earth 5

I want to group by group_code and get the first row with sum(hours) like below:

COMPANY LOT GROUP_CODE LOCATION HOURS
ab1 1 xt mars 9
db1 1 ls earth 7

I tried the code below, but couldn't get the sumary to work. Thanks,

select company,lot,group_code,location,sum(hours) from (
SELECT company
,lot
,group_code
,hours
,location
,ROW_NUMBER () OVER (PARTITION BY group_code ORDER BY company) AS rk
FROM ad
) where rk = 1 group by company, lot, group_code,location

Report message to a moderator

Re: Grouping with sum and retain first row [message #645338 is a reply to message #645337]

Thu, 03 December 2015 14:05

Solomon Yakobson
Messages: 3310
Registered: January 2010
Location: Connecticut, USA

Senior Member

You either use analytics or group by. No need for both. Hint - if you use group by use KEEP FIRST.

SY.

Report message to a moderator

Re: Grouping with sum and retain first row [message #645339 is a reply to message #645337]

Thu, 03 December 2015 14:06

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Previous Topic:	Determine all data between 2 dates
Next Topic:	How to fill a column with given values?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 27 11:41:29 CDT 2026