MySQL query returns wrong COUNT value

Question

Here I have shared my query. All the total values are giving wrong results as very big value numbers which are not the desired COUNT values. Whats wrong in my query ?

           SELECT bd.business_id,
                  bd.business_name,
                  bd.business_address,
                  bd.created_at,
                  bd.updated_at AS extent_expiry_date,
                  bd.manager_id as user_id,
                  COUNT(m.manager_id) AS total_managers,
                  COUNT(a.agent_id) AS total_agents,
                  COUNT(c.customer_id) AS total_customers,
                  COUNT(t.task_id) AS total_tasks,
                  COUNT(t.order_id) AS total_orders
            FROM business_details bd
            LEFT JOIN managers m ON m.business_id = bd.business_id
            LEFT JOIN agents a ON a.business_id = bd.business_id
            LEFT JOIN customers c ON c.business_id = bd.business_id
            LEFT JOIN tasks t ON t.business_id = bd.business_id
            GROUP BY bd.business_id

I'm assuming that all your tables aren't unique on business_id? If not then if a business has 3 managers and 3 agents you're getting 9 rows... have you looked at the raw data for a single business to determine if you're selecting the data that you want? — Ben
– Ben, Commented Jan 1, 2018 at 18:15

M Khalid Junaid · Accepted Answer · 2018-01-01 18:20:29Z

As there is no sample data set in your question, My guess is you are getting the same manager_id,agent_id... multiple times due to left join to get the correct count use distinct

SELECT bd.business_id,
      bd.business_name,
      bd.business_address,
      bd.created_at,
      bd.updated_at AS extent_expiry_date,
      bd.manager_id as user_id,
      COUNT(distinct m.manager_id) AS total_managers,
      COUNT(distinct a.agent_id) AS total_agents,
      COUNT(distinct c.customer_id) AS total_customers,
      COUNT(distinct t.task_id) AS total_tasks,
      COUNT(distinct t.order_id) AS total_orders
FROM business_details bd
LEFT JOIN managers m ON m.business_id = bd.business_id
LEFT JOIN agents a ON a.business_id = bd.business_id
LEFT JOIN customers c ON c.business_id = bd.business_id
LEFT JOIN tasks t ON t.business_id = bd.business_id
GROUP BY bd.business_id

Also include all non aggregated columns in group by becuase as per the new release and most RDBMS rejects these type of queries

GROUP BY 
bd.business_id,
bd.business_name,
bd.business_address,
bd.created_at,
bd.updated_at,
bd.manager_id

Collectives™ on Stack Overflow

MySQL query returns wrong COUNT value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related