Advanced SQL!!! Experts pls Help [message #19071] |
Wed, 27 February 2002 07:18 |
deadlee
Messages: 1 Registered: February 2002
|
Junior Member |
|
|
Here's a question that's been baffling me.
I have in my table a whole bunch of dollar amounts for January and February.
I want to create a report that will give me the amounts for February, as well as YearToDate(YTD) amounts.
I am stuck in creating a query statement.
Here's what I have:
SELECT ApplicationName, Type, InputSpreadsheetFEBtext.[[Planned/Unplanned]], Sum([[Amount $]]) AS SumOfAmount, Sum(LLR) AS SumOfLLR, Sum([[CAD $]]) AS SumOfCAD, Sum(EM) AS SumOfEM
FROM Table WHERE (((Month)="February"))
GROUP BY ApplicationName, Type, [[Planned/Unplanned]];
Now this gives me the records for February.
I also want to add the two following fields.
SumofAmountYTD, and SumofEMYTD.
the situation i am stuck in is HOW?? cuz i have a where clause for February, which I don't need for the YTD numbers?
is there a way Union all would help?
Any possible help.
|
|
|
Re: Advanced SQL!!! Experts pls Help [message #19074 is a reply to message #19071] |
Wed, 27 February 2002 08:16 |
Jon
Messages: 483 Registered: May 2001
|
Senior Member |
|
|
Not precisely what you want, but you'll get the idea...
13:13:25 ==> select * from tableamt;
DATEIN AMTIN
---------- ---------
02/27/2002 1000
02/27/2002 1000
02/27/2002 1000
02/27/2002 1000
02/27/2002 1000
01/28/2002 1000
01/28/2002 1000
01/28/2002 1000
8 rows selected.
13:13:36 ==> select ytdamt, febamt from
13:13:43 2 (select sum(amtin) ytdamt
13:13:43 3 from tableamt),
13:13:43 4 (select sum(amtin) febamt
13:13:43 5 from tableamt where to_char(datein,'MM') = '02');
YTDAMT FEBAMT
--------- ---------
8000 5000
|
|
|