GROUP BY with SUM without removing empty (null) values

Question

TABLES:

Players

player_no   | transaction_id
----------------------------
1           | 11
2           | 22
3           | (null)
1           | 33

Transactions

id            | value   |
-----------------------
11            | 5
22            | 10
33            | 2

My goal is to fetch all data, maintaining all the players, even with null values in following query:

SELECT p.player_no, COUNT(p.player_no), SUM(t.value) FROM Players p 
INNER JOIN Transactions t ON p.transaction_id = t.id 
GROUP BY p.player_no

nevertheless results omit null value, example:

player_no | count  | sum
------------------------
1         | 2      |  7
2         | 1      |  10

What I would like to have is mention about the empty value:

player_no | count  | sum
------------------------
1         | 2      |  7
2         | 1      |  10
3         | 0      |  0

What do I miss here? Actually I use QueryDSL for that, but translated example into pure SQL since it behaves in the same manner.

So there is no record in the Transactions table but you want it returned from the player table anyway? If so change your inner join to left join (you may have to change your sum to do a isnull check sum(IsNull(t.value, 0))) — Brad, Apr 27 '18 at 14:12

score 8 · Accepted Answer · answered Apr 27 '18 at 14:11

8

using LEFT JOIN and coalesce function

SELECT p.player_no, COUNT(p.player_no), coalesce(SUM(t.value),0) 
FROM Players p 
LEFT JOIN Transactions t ON p.transaction_id = t.id 
GROUP BY p.player_no

answered Apr 27 '18 at 14:11

D-Shih

44,943
6
31
51

1

COUNT(p.player_no) is just the same as count(*) here – paparazzo Apr 27 '18 at 14:19
@paparazzo yes.you are right.I think can also write `COUNT(1)` :) – D-Shih Apr 27 '18 at 14:25

score 2 · Answer 2 · answered Apr 27 '18 at 14:12

2

Change your JOIN to a LEFT JOIN, then add IFNULL(value, 0) in your SUM()

answered Apr 27 '18 at 14:12

Aurelien

1,497
7
15

score 2 · Answer 3 · answered Apr 27 '18 at 14:14

left join keeps all the rows in the left table

SELECT p.player_no
     , COUNT(*) as count
     , SUM(isnull(t.value,0)) 
FROM Players p 
LEFT JOIN Transactions t 
       ON p.transaction_id = t.id 
GROUP BY p.player_no

You might be looking for count(t.value) rather than count(*)

score 1 · Answer 4 · answered Apr 27 '18 at 14:33

I'm just offering this so you have a correct answer:

SELECT p.player_no, COUNT(t.id) as [count], COALESCE(SUM(t.value), 0) as [sum]
FROM Players p LEFT JOIN
     Transactions t
     ON p.transaction_id = t.id 
GROUP BY p.player_no;

You need to pay attention to the aggregation functions as well as the JOIN.

score 1 · Answer 5 · answered Apr 27 '18 at 15:12

1

Please Try This:

SELECT P.player_no,
   COUNT(*) as count,
   SUM(isnull(T.value,0)) 
FROM Players P 
LEFT JOIN Transactions T 
   ON P.transaction_id = T.id 
GROUP BY P.player_no

Hope this helps.

answered Apr 27 '18 at 15:12

Ameya

78
1
11

GROUP BY with SUM without removing empty (null) values

5 Answers5