Re: Select query for sum of hours

georgina.wren_at_eu.watsonwyatt.com (George) wrote in message news:<d45230d0.0408100619.1f752793_at_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 -- Received on Wed Aug 11 2004 - 01:37:03 CEST