Nested Sum in Multiple groups

[didajosh]

The table is EARN.

ID----------YEAR----------PAYNO----------HRS
01----------2007-------------01---------------10
01----------2007-------------01---------------15
01----------2007-------------01---------------10
01----------2007-------------02---------------14
01----------2007-------------02---------------10
01----------2007-------------03---------------14
01----------2007-------------03---------------10
02----------2007-------------01---------------12
02----------2007-------------01---------------15
02----------2007-------------01---------------10
02----------2007-------------02---------------14
02----------2007-------------02---------------10
02----------2007-------------03---------------14
02----------2007-------------03---------------10
01----------2008-------------01---------------17
01----------2008-------------01---------------15
01----------2008-------------01---------------11
01----------2008-------------02---------------14
01----------2008-------------02---------------10
01----------2008-------------03---------------14
01----------2008-------------03---------------16
02----------2008-------------01---------------10
02----------2008-------------01---------------15
02----------2008-------------01---------------15
02----------2008-------------02---------------14
02----------2008-------------02---------------10
02----------2008-------------03---------------14
02----------2008-------------03---------------11


User will select the YEAR and the Pay No. at the run time of the query.
(User can also give Pay no. in range i.e. from 01-04)
Say the year is 2008 & Pay No. 03 the desired result should be

ID----------Total HRS
01----------30
02----------25

So, I am looking for the total hours of the user in a particular pay period in a particular year.

Please help, this is urgent, and I am stuck.

Dipali

[amitpatel66]

You can try this query Dipali:

[code=oracle]
SELECT ID,SUM(Hrs) FROM table_name WHERE year = &n AND pay_no = &n
GROUP BY ID;
[/code]

In the above query you can enter year and pay_no at run time and the query should give you desired result

[didajosh]

Hi Amit,
Thank you so much.
I think I was struggling with this for so long that I almost lost perspective...! !!
You were a greatttt help.

Regards,
Dipali

[amitpatel66]

No problem. Its good to hear that the solution helped. Do post back in case of any other issues.

MODERATOR