CODEWITHSUNDEEP
phpmysqlsql
User787665
User787665

Reputation: 337

MySQL SUM from Multiple Tables

I have 2 tables: 'sending_fresh' and 'agents'

I would like to get the total where 'cc' equals a certain date from 'sending_fresh' and a specific team from 'agents'.

I currently have:

SELECT SUM(sending_fresh.cc), agents.team  
FROM sending_fresh, agents 
WHERE agents.team = 'team1' 
AND sending_fresh.date = '2012-05-12'

The sum works fine when selecting from just the 'sending_fresh' table but comes with a much higher and wrong answer when adding a WHERE statement from a second table.

Can anyone help?

Upvotes: 1

Views: 218

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753585

Main answer

The foreign key in sending_fresh is agent_id which corresponds to id in the agents table.

You're missing a JOIN condition and you are missing a GROUP BY clause:

SELECT SUM(s.cc), a.team  
  FROM sending_fresh AS s
  JOIN agents        AS a ON a.id = s.agent_id
 WHERE a.team = 'team1' 
   AND s.date = '2012-05-12'
 GROUP BY a.team;

Before we had the information on the join, I wrote:

SELECT SUM(s.cc), a.team  
  FROM sending_fresh AS s
  JOIN agents        AS a ON a.team = s.team    -- Guess at join columns!
 WHERE a.team = 'team1' 
   AND s.date = '2012-05-12'
 GROUP BY a.team;

Since you missed out the join condition, I'm having to guess what it is. It might instead be a.team_id = s.team_id or something like that instead.

If the join is on sending_fresh.team, you could avoid the join altogether if you wish.

SELECT SUM(cc), team  
  FROM sending_fresh
 WHERE team = 'team1' 
   AND date = '2012-05-12'
 GROUP BY team;

This simplification is not valid now the schema is known in more detail.

General comments

Note that using an explicit JOIN means you should not forget the join condition. You should not use the comma-list of tables notation in the FROM clause. You need to understand it to recognize antique SQL. You should never use it in new SQL and you should upgrade old SQL to use explicit joins instead.

Note that if you have N tables in a query, you should have N-1 join conditions.

Upvotes: 2

Related Questions