CODEWITHSUNDEEP
mysqlsqlsuminner-joinaggregate-functions
kiko2
kiko2

Reputation: 33

How to join two table to display sales for each id

I was trying to display the total sale of each id by combining the two table by using the id. I have two table 1. user table, 2. sales table

//user table

--------------
| id  | name  |
---------------
|  1  | yuki  |
|  2  | soman |
---------------


// sales table

--------------
| id  | total|
---------------
|  1  | 300  |
|  2  | 23   |
|  1  | 500  |
---------------

With my query it only display 1 sale witch is sales for yuki.


SELECT i.name,SUM(ROUND(s.total),2)) AS sales
FROM user i
INNER JOIN sales s
ON i.id = s.id
--------------
| name | sales|
---------------
| yuki | 800  |
---------------

I want to display the output like this, what did I missed from my query?

--------------
| name | sales|
---------------
| yuki | 800  |
|soman | 23   |
---------------

Upvotes: 1

Views: 159

Answers (1)

GMB
GMB

Reputation: 222582

Your query needs a group by clause:

SELECT u.name, SUM(ROUND(s.total),2)) AS sales
FROM user u
INNER JOIN sales s ON s.id = u.id
GROUP BY u.id, u.name

Such error is much easier to spot when sql mode ONLY_FULL_GROUP_BY is enabled.

As an alternative, you might want to consider a correlated subquery, which avoids outer aggregation (it actually behaves like a LEFT JOIN, which is - probably - closer to what you want):

SELECT u.*, 
    (SELECT SUM(ROUND(s.total),2)) FROM sales s WHERE s.id = u.id) AS sales
FROM user u

Side note: user is a language keyword, hence not a good choice for a column name. Consider using something else, such as users for example.

Upvotes: 5

Related Questions