hi , Octavian
you can try this SQL.
select agents.id, agents.name,
(select count(*) from clients where agent=agents.id),
(select sum(value) from sales where agent=agents.id)
> To: mysql@stripped
> From: orasnita@stripped
> Subject: Getting the sum() for a column from a joined table
> Date: Sun, 3 Jan 2010 01:35:49 +0200
> 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
> 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.
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
Keep your friends updated―even when you’re not signed in.