Home » RDBMS Server » Server Administration » Summing a column when including one value is based on the existence of another
Summing a column when including one value is based on the existence of another [message #370951] Sun, 12 March 2000 10:56 Go to next message
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 Go to previous message
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
Previous Topic: Group By: A much faster alternative
Next Topic: dbms_shared_pool command
Goto Forum:
  


Current Time: Thu Oct 22 04:49:11 CDT 2020