Re: Having trouble with a query

answer below

Derek McDonald <mazey_at_home.com> wrote:  

> I am having a ^$#%# of a time with a query for a project.
>
> The table that I have looks like this:
>
> Lname Fname SSN Adult_SSN Balance_due
> -------- -------- ----------- ----------- ------------
> Cass Gary 222-33-4444 4
> Cass Donald 333-44-5555 222-33-4444 0
> Archer Peter 828-22-9876 0
> Archer Sue 987-66-5544 828-22-9876 2.5
> Cass Mark 777-88-9966 222-33-4444 1
> Borland Kevin 555-22-1114 1.5
> Borland Jean 555-22-4441 555-22-1114 0
>
> The entries with the adult_ssn's are children that the parent
> (the entry with the same ssn as the adult_ssn) are responsible
> for. I need to show the lname, fname, and sum of balances due
> for the parent, with the balances due of their kids included.
> I have been working on this one for many hours, and either am having
> a problem where it is counting the balance of Cass double, or since I
> have to show the fname also, grouping it by lname, fname. I know that I
> should use a count somewhere in the join to combine the kids
> balance_due, but haven't been able to figure this one out.
>
> the final outcome should look like this
>
> Lname Fname sum(balance_due)
> ------ ------ ---------------
> Archer Peter 2.5
> Borland Kevin 1.5
> Cass Gary 5
>
> Does anyone out there know how to solve this one?

Derek,

Try the query below. I'm not sure if it is efficient on large tables (In fact I think it's not), but it seems to work for the testdata you provided.  

select lname

      ,fname
      ,s_balance_due 
from (Select parent.balance_due
            ,parent.Lname lname 
            ,parent.Fname fname
            ,sum(parent.balance_due + child.balance_due) 
             - ((count(parent.ssn)-1) * parent.balance_due) s_balance_due
      from my_table child
          ,my_table parent
      where parent.ssn = child.adult_ssn
      group by parent.Lname
              ,parent.Fname
              ,parent.balance_due);

Regards,

Martin Received on Mon Dec 07 1998 - 08:52:31 CST