mysql count NULL

Question

I have a game1 table, it seems like this:

player1	player2	result_1	result_2
team1	team2	10	NULL
team1	team3	10	NULL
team2	team3	6	NULL

result_1 is player1's result and result_2 is player2's result.

I want to make below table:

player	count_null
team1	0
team2	1
team3	2

Please help me

Look into SQL GROUP BY if you want to come up with a solution on your own — DownloadPizza
– DownloadPizza, Commented Jun 21, 2022 at 12:41
what does the count_null column represent here? what is 0/1/2 for count_null ? is it by some custom logic or calculation? — Monish Khatri
– Monish Khatri, Commented Jun 21, 2022 at 12:42

Saar Levy · Accepted Answer · 2022-06-21 12:54:08Z

1

You can do that by "dividing" the table columns into more rows, then group by player (team) and then sum 1 when the result is null

SELECT player, sum(if(result is null, 1, 0))
FROM (
  SELECT player1 as player, result_1 as result
  FROM game1
  UNION ALL
  SELECT player2 as player, result_2 as result
  FROM game1) t
GROUP BY player

edited Jun 21, 2022 at 12:54

answered Jun 21, 2022 at 12:47

Saar Levy

3302 silver badges6 bronze badges

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

1 Comment

Akina Over a year ago

IF() is obviously excess, sum(if(result is null, 1, 0)) can be replaced with SUM(result IS NULL).

Collectives™ on Stack Overflow

mysql count NULL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related