MySQL | Trouble Combining JOIN, GROUP BY, and HAVING SUM

August 11, 2022

I’m trying to aggregate data from two tables and having trouble displaying it correctly. I searched for an answer to my question on stackoverflow, MySQL documentation, and several tutorials with no luck. Anyway, here’s what I got.

SELECT works_with.client_id AS 'Client ID',
client.client_name AS 'Client Name',
works_with.total_sales AS 'Total Sales'
FROM works_with
JOIN client
ON works_with.client_id = client.client_id
GROUP BY works_with.client_id HAVING SUM(works_with.total_sales);

client:

client_id	client_name	branch_id
400	Dunmore Highschool	2
401	Lackawana Country	2
402	FedEx	3
403	John Daly Law, LLC	3
404	Scranton Whitepages	2
405	Times Newspaper	3
406	FedEx2	2

works_with:

emp_id	client_id	total_sales
102	401	267000
102	406	15000
105	400	55000
105	404	33000
105	406	130000
107	403	5000
107	405	26000
108	402	22500
108	403	12000

My goal is to join client.client_id, client.client_name, and works_with.total_sales. Additionally, I would like to eliminate duplicate clients while maintaining their total_sales numbers.

Like this:

client_id	client_name	total_sales
400	Dunmore Highschool	55000
401	Lackawana Country	267000
402	FedEx	22500
403	John Daly Law, LLC	17000
404	Scranton Whitepages	33000
405	Times Newspaper	26000
406	FedEx2	145000

But what I get is:

client_id	client_name	total_sales
400	Dunmore Highschool	55000
401	Lackawana Country	267000
402	FedEx	22500
403	John Daly Law, LLC	5000
404	Scranton Whitepages	33000
405	Times Newspaper	26000
406	FedEx2	15000

It looks like it’s successfully grouping by client_id but doesn’t perform the ADD function.

PS: If this ends up being a terrible question, can someone please explain why? I got downvoted on my last question and tried putting more effort into this one.

>Solution :

It’s not a bad question at all… well at least I think so 😉

At a glance, I think you’re actually closer to what you’re trying to achieve than you think. And if I’ve understood you correctly, then the below should give you what you’re looking for.

SELECT
works_with.client_id AS 'Client ID',
client.client_name AS 'Client Name',
SUM(works_with.total_sales) AS 'Total Sales'
FROM works_with
JOIN client ON works_with.client_id = client.client_id
GROUP BY works_with.client_id;

The GROUP BY will ensure that you only get 1 record of each client with the total sales summed for each client id.