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.