Getting the sum() for a column from a joined table

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=gcdmg-mysql-2@m.gmane.org

RE: Getting the sum() for a column from a joined table

--_e725fbe4-8f73-4e03-a101-f1c0efb5dd7c_
Content-Type: text/plain; charset="gb2312"
Content-Transfer-Encoding: 8bit


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: orasnita@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=liuyann@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:WLM TAGL:ON:WL:en-xm:SI_SB_5:092010
--_e725fbe4-8f73-4e03-a101-f1c0efb5dd7c_--

Re: Getting the sum() for a column from a joined table

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" wrote in message
news:SNT102-W5962ED9A8E5CB50A275855CB750@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: orasnita@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=liuyann@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:WLM TAGL: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=gcdmg-mysql-2@m.gmane.org