Select query for sum of hours

[George]

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

[Mark D Powell]

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 --

[Christine]

> 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'

[Pratap]

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