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) from agents where agent.id=100 ========================================= best regards ACMAIN > To: mysql@lists.mysql.com > From: orasn...@gmail.com > Subject: Getting the sum() for a column from a joined table > Date: Sun, 3 Jan 2010 01:35:49 +0200 > > 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 > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=liuy...@live.com > _________________________________________________________________ Keep your friends updated―even when you’re not signed in. http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_5:092010
