0

I am building a query where I need to join three tables.

Here are my tables

agents(id, full_name)
agent_orders(id, agent_id, [..])
agent_targets(id, agent_id, month_target, amount)

The month_target in agent_orders is a DATE field, I am interested only in the month and year portion, but I added the day for convenience.

Here is my query

SELECT
    agents.full_name,
    COUNT(DISTINCT orders.id) total_orders,
    COUNT(DISTINCT yearly_targets.id) nr_yearly_targets,
    SUM(yearly_targets.amount) yearly_target,
    month_target.amount monthly_target
FROM agent_targets yearly_targets
LEFT JOIN agents ON yearly_targets.agent_id = agents.id AND YEAR(yearly_targets.month) = YEAR(CURRENT_DATE())
LEFT JOIN agent_targets month_target ON month_target.agent_id = agents.id AND MONTH(month_target.month) = MONTH(CURRENT_DATE()) AND YEAR(month_target.month) = YEAR(CURRENT_DATE())
LEFT JOIN agent_orders orders ON orders.agent_id = agents.id
GROUP BY agents.id

The problem is that yearly_target is not the right amount. Every other data is correct. I tried a couple of things and the problem occurs when I join the orders table. If I don't join the orders table, the data is correct.

How can I solve this ? The yearly_target is always a huge number that does not rappresents the correct value.

Thank you

C. Ovidiu
  • 1,104
  • 2
  • 18
  • 38
  • Edit your question to add: 1) all tables, where is yearly_targets? 2) sample data set so we can try. 3) sample desired output. 4) problem expressed on previous data. Thanks! – Nic3500 Nov 02 '17 at 15:26
  • @Nic3500 I will add sample data, but yearly_targets is the alias for the table agent_targets. I join the agent_targets twice, once to get the current month, and once to sum all yearly amounts – C. Ovidiu Nov 02 '17 at 15:28
  • What is `yearly_targets.month`? Should that be `yearly_targets.month_target`? – Barmar Nov 02 '17 at 15:59
  • Same with `month_target.month`. Maybe the actual name of the column is just `month`? – Barmar Nov 02 '17 at 16:01

0 Answers0