List:General Discussion« Previous MessageNext Message »
From:Octavian Râºniþã Date:January 2 2010 11:35pm
Subject:Getting the sum() for a column from a joined table
View as plain text  
Hi,

I have 3 tables, `agents`, `clients` and `sales` and I want to select a 
single agent from the `agents` table, and 2 more columns that contain the 
number of clients for the selected user (from the `clients` table) and the 
sum of the sales for the selected user (from the `sales` table).

Is it possible to do this selection in a single query?

I have tried using:

select agents.id, agents.name, count(clients.name), sum(sales.value)
from agents
left join clients on agents.id=clients.agent,
left join sales on agents.id=sales.agent
where agent.id=100
group by clients.agent, sales.agent;

But it doesn't give good results.
The sum of sales is bigger than it should be... kind of multiplied with the 
number of clients that match, like if there were no group by columns 
specified.

I have tried to group by more other columns like clients.id and sales.id or 
agents.id, but with no good results.

Thank you for your help.

Octavian


Thread
Getting the sum() for a column from a joined tableorasnita3 Jan
  • RE: Getting the sum() for a column from a joined tableLIU YAN3 Jan
  • Re: Getting the sum() for a column from a joined tableOctavian Ra?ni??3 Jan