CODEWITHSUNDEEP
phpmysqljoinsum
C. Ovidiu
C. Ovidiu

Reputation: 1134

Mysql: SUM joined table value

I have a problem with joins and SUM().

I have two tables, agents(id, name) and orders(id, agent_id, total).

Now, I need a list of all agents counting how many orders and the total of all orders they placed. Here is my basic query:

SELECT
    agents.*,
    COUNT(DISTINCT orders.id) total_orders,
    SUM(orders.total) total_amount
FROM agents
LEFT JOIN orders ON agents.id = orders.agent_id
GROUP BY agents.id

The total_orders is correct, but the total_amount is not. Every agent has the wrong SUM() and even those who didn't order anything have a value.

Is it possible in one query? I don't want to query again in a loop.

Upvotes: 0

Views: 38

Answers (1)

Roberto
Roberto

Reputation: 2185

You don't need a left join, replace LEFT JOIN by JOIN:

SELECT
    agents.*,
    COUNT(DISTINCT orders.id) total_orders,
    SUM(orders.total) total_amount
FROM agents
JOIN orders ON agents.id = orders.agent_id
GROUP BY agents.id

The wrong result you were getting is caused by the fact that a left join will retrieve a row from orders table even if there is no record that has an agent_id equal to agents.id

A single join, on the other hand, will not retrieve agents that don't have orders.

Oh, then you need from all agents and want 0 for the total for those that don't have orders... then the left join should work. Otherwise you could do this:

SELECT
    agents.*,
    COUNT(DISTINCT orders.id) total_orders,
    SUM(CASE WHEN orders.id IS NULL then 0 ELSE orders.total END) total_amount
FROM agents
LEFT JOIN orders ON agents.id = orders.agent_id
GROUP BY agents.id

Upvotes: 1

Related Questions