Sum() for relating table columns

[fcoomermd]

Here is what I am having troubles with.
I have a table "SalesAgents", and I have another table called "SoldProducts".
I want to select the salesAgents (*), and a summary of their sold products (two types, computer, monitor)

The relationship is 1 to many (SalesAgents to SoldProducts).
I want the statement to return something like this

SalesAgentInfo ie (firstName, lastName, etc.) as well as the products sold in sum format ie (computers=7, monitors=8)

I have been trying to do it the Inner Join way, but have had no success...

Any help would be apprciated

Thanks
FC

 

[Pattycake245]

This is assuming some of the columns in your tables but something like;

select a.agentID, a.firstname, a.lastname, b.computers_sold,
b.monitors_sold
from SalesAgents a join
(select agentID, sum(computers) computers_sold, sum(monitors) monitors_sold from SoldProducts group by agentID) b
on a.agentID=b.agentID
order by agentID

Tim

 

[vongrunt]

With individual subqueries

select blah blah blah,
(select count(*) from SoldProducts where ProductType='Computer' and AgentID= SA.AgentID) as computers,
(select count(*) from SoldProducts where ProductType='Monitor' and AgentID= SA.AgentID) as monitors
from SalesAgents SA
order by whatever

With derived table:

select SA.blah blah blah,
SP.computers, SP.monitors
from
SalesAgents SA
left join
(	select AgentID,
	sum(case when ProductType='Computer' then 1 else 0) as computers,
	sum(case when ProductType='Monitor' then 1 else 0) as monitors
	from SoldProducts
	group by AgentID
) SP on SA.AgentID=SP.AgentID
order by whatever

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.