Re: Having trouble with a query

From: Shatalov Vadim <skiv_at_admkom.tlt.ru>
Date: Tue, 8 Dec 1998 09:28:00 +0400
Message-ID: <qq1j47.8m.ln@news.admcity.tlt.ru>

Derek McDonald wrote in message <366BA267.3747688F_at_home.com>...
>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?
table testd(
name varchar2(25),
lname varchar2(25),
ssn varchar2(11),
pssn varchar2(11),
balance number)

select t1.name, t1.lname, t2.sumbalance+t1.balance as FullBalance from (select * from testd where testd.PSSN is null)t1, (select pssn,sum(balance)as sumbalance from testd group by pssn)t2 where t1.ssn=t2.pssn;

2 Beer is not 2 Bear !

---

Shatalov Vadim,
Oracle DBA/Developer ,Russia,Togliatti, Phones : +7(8469)245417
mail : skiv_at_admkom.tlt.ru

begin 666 Vadim Petrovich Shatalov.vcf
M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..E-H871A;&]V.U9A9&EM.U!E M=')O=FEC: T*1DXZ5F%D:6T_at_4&5T<F]V:6-H(%-H871A;&]V#0I414P[5T]2 M2SM63TE#13HW+3_at_T-CDM,C0U-#$W#0I!1%([2$]-13H[.SL[.SM2=7-S:6$- M"DQ!0D5,.TA/344Z4G5S<VEA#0I%34%)3#M04D5&.TE.5$523D54.G-K:79 M861M:V]M+G1L="YR=0T*14U!24P[24Y415).150Z=&]P+6UO9&5L0&%T=&%C G:RYR=0T*4D56.C$Y.3_at_Q,C X5# U,# U.5H-"D5.1#I60T%21 T* `
end Received on Mon Dec 07 1998 - 23:28:00 CST