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)
left join clients on agents.id=clients.agent,
left join sales on agents.id=sales.agent
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.