470,573 Members | 1,668 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,573 developers. It's quick & easy.

Select query for sum of hours

Hi,

The sql query below returns data in the 'note' field from a table
called ww_rec_1 from the previous month and displays the sum of hours
for each category.

SELECT note,SUM(hours)FROM WW_REC_1
where trans_date between
last_DAY(ADD_MONTHS(SYSDATE, -2))+1
and LAST_DAY(ADD_MONTHS(SYSDATE,-1))
GROUP BY NOTE

This is the returned data:

"NOTE", "SUM(HOURS)"

"ACT", -330864.75
"CHG", -165.25
"EXT", 331108.5
"INT", 45

I need to add together the sum of hours for specified data in the note
field. So in this case I want to add together the sum of hours for
'ACT' and 'CHG'. I also need to add together the sum of hours for
'EXT' and 'INT'.

Can anyone help me out with this?

Thanks in advance

George
Jul 19 '05 #1
3 15142
ge***********@eu.watsonwyatt.com (George) wrote in message news:<d4**************************@posting.google. com>...
Hi,

The sql query below returns data in the 'note' field from a table
called ww_rec_1 from the previous month and displays the sum of hours
for each category.

SELECT note,SUM(hours)FROM WW_REC_1
where trans_date between
last_DAY(ADD_MONTHS(SYSDATE, -2))+1
and LAST_DAY(ADD_MONTHS(SYSDATE,-1))
GROUP BY NOTE

This is the returned data:

"NOTE", "SUM(HOURS)"

"ACT", -330864.75
"CHG", -165.25
"EXT", 331108.5
"INT", 45

I need to add together the sum of hours for specified data in the note
field. So in this case I want to add together the sum of hours for
'ACT' and 'CHG'. I also need to add together the sum of hours for
'EXT' and 'INT'.

Can anyone help me out with this?

Thanks in advance

George


First, this probably should have been posted to
comp.databases.oracle.misc rather than the obsolete
comp.database.oracle group. When a newsgroup has subgroups you should
generally post to the subgroups and not the group.

There are several ways to solve you query. One method would be to
change the from clase to be a FROM (Select .... where in the new
select you use a case statement to return ACT for CHG and EXT for INT
so that to the outer query the NOTE values are the same group value.

HTH -- Mark D Powell --
Jul 19 '05 #2
> SELECT note,SUM(hours)FROM WW_REC_1
where trans_date between
last_DAY(ADD_MONTHS(SYSDATE, -2))+1
and LAST_DAY(ADD_MONTHS(SYSDATE,-1))
GROUP BY NOTE

This is the returned data:

"NOTE", "SUM(HOURS)"

"ACT", -330864.75
"CHG", -165.25
"EXT", 331108.5
"INT", 45

I need to add together the sum of hours for specified data in the note
field. So in this case I want to add together the sum of hours for
'ACT' and 'CHG'. I also need to add together the sum of hours for
'EXT' and 'INT'.


1. add together ...
SELECT SUM(hours) FROM WW_REC_1
where trans_date between
last_DAY(ADD_MONTHS(SYSDATE, -2))+1
and LAST_DAY(ADD_MONTHS(SYSDATE,-1))
where note='ACT' OR note='CHG'

2. also add together ...
SELECT SUM(hours) FROM WW_REC_1
where trans_date between
last_DAY(ADD_MONTHS(SYSDATE, -2))+1
and LAST_DAY(ADD_MONTHS(SYSDATE,-1))
where note='EXT' OR note='INT'
Jul 19 '05 #3
select sum ( sum_hours ),
note_group
from (
select decode
(
note,
'ACT', 'ACT-CHG',
'CHG', 'ACT-CHG',
null
) note_group,
decode
(
note,
'EXT', 'EXT-INT',
'INT', 'EXT-INT',
null
) note_group,
sum_hours
from (
SELECT note,SUM(hours) sum_hours FROM WW_REC_1
where trans_date between
last_DAY(ADD_MONTHS(SYSDATE, -2))+1
and LAST_DAY(ADD_MONTHS(SYSDATE,-1))
GROUP BY NOTE
)
)
group by
note_group;
Pratap
Cognizant Technology Solutions, India
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Tcs | last post: by
1 post views Thread by davidboyd24 | last post: by
1 post views Thread by Rob Woodworth | last post: by
3 posts views Thread by George | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.