| Summing a column when including one value is based on the existence of another [message #370951] |
Sun, 12 March 2000 10:56  |
B.Courtney
Messages: 1
Registered: March 2000
|
Junior Member |
|
|
I am trying to write a query where I sum a column grouped by invoice number. There is one row that would or would not be included based on the existence of another row. For instance the table would look like this.
Table XYZ
with columns: InvoiceSum, ItemDesc, and Sales$
and values
123, A, $5
123, B, $10
123, C, $8
123, D, $3
123, E, $2
Lets say that you would sum all of Sales$ without item
A if item E exists. If item E doesn't exist
you would sum all of the Sales$ including item A.
I use Oracle Discoverer and it won't allow PL/SQL
statements so I am trying to do this with a SQL statement. Thanks for any help.
|
|
|
|
| Re: Summing a column when including one value is based on the existence of another [message #370953 is a reply to message #370951] |
Mon, 13 March 2000 08:19  |
Mark E Kane
Messages: 7
Registered: January 2000
|
Junior Member |
|
|
B.
This can be done with sql using a nexted select. I think the following might do it:
select InvoiceSum, sum(Sales$)
from XYZ a
where 0 = (select count(*)
from XYZ b
where ItemDesc = 'E'
and a.InvoiceSum = b.InvoiceSum)
or ItemDesc != 'A'
group by InvoiceSum;
The where clause goes something like this:
if there are no rows with 'E', sum all rows, otherwise sum all rows without 'A' in the ItemDesc.
I haven't run this on a test table, so if it doesn't work or it wasn't what you were looking for: give a shout.
Mark
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
