Thank you for this solution. It is working, although I was hoping that it could be possible to do it without sub-selects because I want to implement it in an ORM that doesn't fully supports sub-selects yet.
-- Octavian "LIU YAN" <liuy...@live.com> wrote in message news:snt102-w5962ed9a8e5cb50a275855cb...@phx.gbl... > > 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 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
