CODEWITHSUNDEEP
sqloracle-database
Dragon Wolf
Dragon Wolf

Reputation: 61

SQL: getting sum from one table, one column, different rows

This is the last part of a project for my basic SQL course I am currently enrolled in. The professor has not responded to me in days, and it is due tomorrow.

So I have a weird issue I need some explanation on. I have a table CUSTOMER with CID, SPONSORID, LAST_NAME, and BALANCE_DUE. Some customers (cid) sponsor (sponsorID) other customers (cid), and the sponsor is responsible for their balance_due and the balance_due of the customer they sponsor. The friend being sponsored contains the CID of the customer that sponsors them under the SPONSORID column in the friends row on the table. Not all sponsors have a balance_due, but the customer they sponsor may have a balance_due, and vice-versa. I need to create a query that returns the last name of the sponsor, their aggregate friend total, their personal total due, and the TOTAL total due (summing up both what they owe and what their friends owe) using just the last name of the sponsor.

The following code (and result when ran) seemed to work thus far, but for some reason, it is summing up the value for the sponsor Finch (what she owes herself) TWICE (she owes 3.75, but the sum function keeps returning 7.50. The Sum(a.BALANCE_DUE) column is the aggregate correct total for the balance due of the customers she sponsors, but the Sum(b.BALANCE_DUE) column somehow added what she owed twice. The amount shown for Rush is correct, not added twice, like the sum for Finch (rush also has friends, but none owe anything, so only her balance came up) I think somehow because Finch owes some herself, and her friends also owe, the query is summing her personal amount twice because of this, but I just don't understand how.....any pointers? 

select b.LAST_NAME, Sum(a.BALANCE_DUE), Sum(b.balance_due)
from CUSTOMER a, CUSTOMER b
where a.SPONSORID = b.CID AND b.BALANCE_DUE is not null 
group by b.LAST_NAME;
                    [ Wrote 4 lines ]
SQL> start q11.sql
LAST_NAME  SUM(A.BALANCE_DUE) SUM(B.BALANCE_DUE)
---------- ------------------ ------------------
Rush                                        8.25
Finch                      30                7.5

Also, as mentioned, the final output needs to have one last column that totals the overall due for the sponsor based on what they owe AND what the sponsored individual owes (basically adding columns Sum(a.balance_due) and Sum(b.balance_due) putting that output in its own column in the same output table (one query)....still trying to think about how to do this, reviewing my notes......but the aggregate function definitely needs to work correctly first.

NOTE: I know this is an ANSI87 type join, its the professors preference, and its a huge headache.....

Cant add to comments: here's the customer table, no duplicates appear

SQL> select * from customer;

   CID FIRST_NAME LAST_NAME  CATEGORY    SPONSORID BALANCE_DUE

 10001 Rita       Rush       Faculty                      8.25
 10002 Becky      Finch      Faculty                      3.75
 10003 Joyce      Xx_Nestler Friend          10002          20
 10004 Wolf       Xx_Nestler Student                         2
 10005 Vicky      White      Student                       6.3
 10006 John       Kline      Student
 10007 Anya       Rush       Student                       3.5
 10008 Carol      Crane      Friend          10002          10
 10009 Tricia     Tatum      Faculty
 10010 Brian      Price      Student
 10011 Lucy       Nash       Faculty                      1.75
 10012 Larry      Bell       Friend          10001
 10013 Ron        Welsh      Friend          10009

13 rows selected.

Upvotes: 0

Views: 90

Answers (2)

William Robertson
William Robertson

Reputation: 15991

How about this:

select sp.last_name
     , sum(cu.balance_due) as customer_balance
     , sp.balance_due as sponsor_balance
from   customer sp, customer cu
where  cu.sponsorid = sp.cid
and    sp.balance_due is not null 
group by sp.last_name, sp.balance_due;

LAST_NAME  CUSTOMER_BALANCE SPONSOR_BALANCE
---------- ---------------- ---------------
Rush                                   8.25
Finch                    30            3.75

You want the one value that exists per sponsor, so just don't sum it.

With ANSI joins it would look like this:

select sp.last_name
     , sum(cu.balance_due) as customer_balance
     , sp.balance_due as sponsor_balance
from   customer sp
       join customer cu on cu.sponsorid = sp.cid
where  sp.balance_due is not null 
group by sp.last_name, sp.balance_due;

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

Try the following query to avoid duplicate values

Select a.last_name,
       (Select sum(b.balance_due)
             From customer b
             Where b.sponsorid = a.cid
       ),
       a.balance_due
From customer a
Where a.balance is not null

Another option is the following (I'm using the old join syntax since you request it)

select a.last_name, t.sponsor_sum, a.balance_due
from customer a,
(
   Select sponsorid, sum(balance_due) sponsor_sum
   From customer
   group by sponsorid
) t
where a.cid = t.sponsorid

Upvotes: 0

Related Questions