List:General Discussion« Previous MessageNext Message »
From:LIU YAN Date:January 3 2010 2:10am
Subject:RE: Getting the sum() for a column from a joined table
View as plain text  
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@stripped
> From: orasnita@stripped
> 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=1
> 
 		 	   		  
_________________________________________________________________
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
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