Get SUM another table off all rows in MySQL

Question

I have database value, for example

table name: a_table

===================================
| userId       | userName         |
===================================
===================================
| abc          | Alice            |
| bcd          | Rachel           |
| efg          | Raymond          |
===================================

table name: b_transaction

=============================================
| transCode     | userId       | value      |
=============================================
=============================================
| 1             | abc          | 100        |
| 2             | abc          | -200       |
| 3             | abc          | 300        |
=============================================

My goal is, get sum of all data, if user dont have row in table transaction, they must be 0. But when I try this query

SELECT a.userId, a.userName, SUM(b.value)
FROM a_table a
LEFT JOIN b_transaction b ON a.userId = b.userId

The result just return 1 row

================================================
| userId       | userName         | value      |
================================================
================================================
| abc          | Alice            | 200        |
================================================

How to achieve that? Thaanks~

you need to add at the end of query, GROUP BY a.userId

Zeljka
– Zeljka

2020-01-14 09:46:54 +00:00
Commented Jan 14, 2020 at 9:46 — Zeljka
– Zeljka, Commented Jan 14, 2020 at 9:46

ev350 · Accepted Answer · 2020-01-14 09:48:04Z

2

You could try grouping the results on the users over the summing of the transactions, like this?

SELECT a.userId, a.userName, SUM(b.value)
FROM a_table a
LEFT JOIN b_transaction b ON a.userId = b.userId
GROUP BY a.userId, a.userName

answered Jan 14, 2020 at 9:48

ev350

4393 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jens · Accepted Answer · 2020-01-14 09:52:12Z

1

add group by:

SELECT a.userId, a.userName, IFNULL (SUM(b.value),0)
FROM a_table a
LEFT JOIN b_transaction b ON a.userId = b.userId
GROUP BY  a.userId, a.userName

see the sqlfiddle

edited Jan 14, 2020 at 9:52

answered Jan 14, 2020 at 9:44

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Comments

Bloafer · Accepted Answer · 2020-01-14 09:48:45Z

1

Using a simple group by statement in your SQL should create the desired result for example:

SELECT a.userId, a.userName, SUM(b.value)
FROM a_table a
LEFT JOIN b_transaction b ON a.userId = b.userId GROUP BY a.userId

answered Jan 14, 2020 at 9:48

Bloafer

1,3287 silver badges12 bronze badges

1 Comment

Jens Over a year ago

Always groub all columns that are part of the SELECT clause. Else you get an error in most databases

Cid · Accepted Answer · 2020-01-14 09:49:43Z

1

You can use IFNULL to check if the result of the sum is null (that is the case if there is no reference for a row from a to b) or not, and then apply a default value :

SELECT a.userId, a.userName, IFNULL(SUM(b.value), 0) AS 'Sum'
FROM a_table a
LEFT JOIN b_transaction b ON a.userId = b.userId
GROUP BY a.userId, a.userName

This outputs :

| userId | userName | Sum |
| ------ | -------- | --- |
| abc    | Alice    | 200 |
| bcd    | Rachel   | 0   |
| efg    | Raymond  | 0   |

answered Jan 14, 2020 at 9:49

Cid

15.3k4 gold badges33 silver badges49 bronze badges

2 Comments

Zeljka Over a year ago

I think this is not correct IFNULL(SUM(b.value), 0) it should be SUM (IF(b.value is not null, b.value, 0)) because you are overriding 'Sum' value

Cid Over a year ago

@Zeljka This is correct

Collectives™ on Stack Overflow

Get SUM another table off all rows in MySQL

4 Answers 4

Comments

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related