Combining JOIN, GROUP BY, and HAVING SUM-CodePudding

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:

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 groups by client_id but doesn't ADD.

CodePudding user response：

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.