MySql SUM and JOIN

I am trying to count sales made by a list of sales agents, this count is made every few minutes and updates a screen showing a 'sales leader board' which is updates using a Ajax call in the background.

I have one table which is created and populated every night containing the agent_id and the total sales for the week and month. I create a second, temporary table, on the fly which counts the sales for the day.

I need to combine the two tables to create a current list of sales for all agents in agent_count.

Table agent_count;

agent_id (varchar),
team_id (varchar),
name (varchar),
day(int),
week(int), 
month(int)

Table sales;

agent_id (varchar),
day(int)

I can't figure out how to combine these tables. I think I need to use a join as all agents must be returned - ev开发者_Go百科en if they don't appear in the agent_count table.

First I make a simple call to get the week and month totals for all agents

SELECT agent_id, team_id, name, week, month FROM agent_count;

the I create a temporary table of todays sales, and then I count the sales for each agent for the day

CREATE TEMPORARY TABLE temp_todays_sales
SELECT s.id, s.agent_id
FROM sales s 
WHERE DATEDIFF(s.uploaded, NOW()) = 0 
AND s.valid = 1;

SELECT tts.agent_id, COUNT(tts.id) as today 
FROM temp_todays_sales tts
GROUP BY tts.agent_id;

What is the best/easiet way to combine these to end up with a resultset such as

agent_id, team_id, name, day, week, month

where week and month also include the daily totals

thanks for any help!

Christy

---

SELECT  s.agent_id, ac.team_id, ac.name,
        s.`day` + COALESCE(ac.`day`, 0) AS `day`,
        s.`day` + COALESCE(ac.`week`, 0) AS `week`,
        s.`day` + COALESCE(ac.`month`, 0) AS `month`
FROM    sales s
LEFT JOIN
        agent_count ac
ON      ac.agent_id = s.agent_id

team_id and name will be NULL if there is no record in agent_count for an agent.

If the agents can be missing from both tables, you normally would need to make a FULL JOIN but since MySQL does not support the latter you may use its poor man's substitution:

SELECT  agent_id, MAX(team_id), MAX(name),
        SUM(day), SUM(week), SUM(month)
FROM    (
        SELECT  agent_id, NULL AS team_id, NULL AS name, day, day AS week, day AS month
        FROM    sales
        UNION ALL
        SELECT  *
        FROM    agent_count
        ) q
GROUP BY
        agent_id