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 3 15443 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 --
> 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'
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Tcs |
last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I...
|
by: Andrew McNab |
last post by:
Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a...
|
by: davidboyd24 |
last post by:
Im currently having difficulty trying to write an SQL query in Access
which will add two columns together to calculate a totals column. For
example...
I was hoping to get the below resultset...
|
by: marco |
last post by:
Dear List,
as it seems, MS SQL as used in Access does not allow a select INTO
within a UNION query. Also, it seems that a UNION query can not be used
as a subquery.
Maybe my (simplified)...
|
by: Rob Woodworth |
last post by:
Hi,
I'm having serious problems getting my report to work. I need to
generate a timesheet report which will contain info for one employee between
certain dates (one week's worth of dates). I...
|
by: Noob |
last post by:
ftp://ukcassassin:winston@www.ukcassassin.pwp.blueyonder.co.uk/htdocs/Diary%20Form.bmp
Hi all
I will appologise in advance for my lack of knowledge of access and its working as i am quite new to...
|
by: djpaul |
last post by:
Hello guys,
I'm busy writing a website for dj's.
But i wanted to show the picture and more stuff of the dj who's playing music between a few hours.
Let's say he works between 18:00 and 21:00 on...
|
by: Mike1961 |
last post by:
Hi there.
Well the situation is this, I have two tables:
1) tbl_registry where rows with code = 123 are 35 ;
1) tbl_Registered where rows with code = 123 are 43
In tbl_Registered I have...
|
by: George |
last post by:
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...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |