CODEWITHSUNDEEP
mysqlsqlgroup-bysum
Howard Zoopaloopa
Howard Zoopaloopa

Reputation: 3822

MYSQL Summing of Column With Unique Results

I've got a many to many table p_to_t

+----------+-----------+-----------+
|   p      |    t      |   weight  |
+----------+-----------+-----------+
|   1      |   bob     |    40     |
+----------+-----------+-----------+
|   2      |   sue     |    24     |
+----------+-----------+-----------+
|   3      |   bob     |    90     |
+----------+-----------+-----------+
|   4      |   joe     |    55     |
+----------+-----------+-----------+
|   5      |   bob     |    33     |
+----------+-----------+-----------+

I'm looking to query for value t WHERE p IN(1,2,4,5) while summing the weight for each value of t.

This query: "SELECT t, SUM(weight) AS sWeight FROM p_to_t WHERE p IN(1,2,4,5)"; just adds all the sums to the first "t" selected e.g;

+-----------+-----------+
|    t      |   weight  |
+-----------+-----------+
|   bob     |   152     |
+-----------+-----------+

When what I would like is:

+-----------+-----------+
|    t      |   weight  |
+-----------+-----------+
|   bob     |   97      |
+-----------+-----------+
|   joe     |   55      |
+-----------+-----------+

Thoughts?

Upvotes: 0

Views: 56

Answers (3)

Sarma Mullapudi
Sarma Mullapudi

Reputation: 110

Yes you can add just Group by to your actual query. then you will get the below result.

SELECT t, SUM(weight) AS sWeight 
 FROM p_to_t 
WHERE p IN(1,2,4,5)
Group by t;

+-----------+-----------+
|    t      |   weight  |
+-----------+-----------+
|   bob     |   73      |
+-----------+-----------+
|   joe     |   55      |
+-----------+-----------+
|   sue     |   24      |
+-----------+-----------+

Upvotes: 0

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

SELECT t, SUM(weight) AS sWeight FROM p_to_t WHERE p IN(1,2,4,5) GROUP BY t

FIDDLE

Upvotes: 1

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

Just add a GROUP BY clause on t

SELECT 
 t, 
 SUM(weight) AS sWeight 
FROM p_to_t 
WHERE p IN(1,2,4,5)
GROUP BY t

By the way, you will also get Sue with these values in the IN clause...

And not 97 for Bob, as 40 + 33 are more often 73

Upvotes: 5

Related Questions