join? union? apples and oranges

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

join? union? apples and oranges

Author

Topic

igmox
Starting Member

3 Posts

Posted - 2008-01-27 : 16:51:06

hi, i'm a newbie and trying to form a query for hours
any help appreciated.

Tables
Persons(ID,Name)
Oranges(PersonsID,Quantity)
Apples (PersonsID,Quantity)

i need to see total numbers of oranges and apples of each person on persons table, for example

personsid name sumoforanges sumofapples
1 lisa 60 20
2 mary 0 15
3 jane 15 0
4 tom 0 0

thanks...

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-01-27 : 17:15:54

select

sum(o.quantity), sum(a.quantity), distinct (p.personid

)
from persons as p join

oranges as o on p.id = o.personid
join apples as a on p.id=a.personid

group by p.id--, a.quantity, o.quantity

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-27 : 18:53:18

[code]
select
p.ID,
Name = max(p.Name),
Apple_Qty = sum(isnull(a.Quantity,0)),
Orange_Qty = sum(isnull(o.Quantity,0))
from
Persons p
left join
Apples a
on p.ID = a.PersonsID
left join
Oranges o
on p.ID = o.PersonsID
group by
p.ID
[/code]

CODO ERGO SUM

igmox
Starting Member

3 Posts

Posted - 2008-01-28 : 08:17:27

thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources