CODEWITHSUNDEEP
sqlpostgresqlpsql
John
John

Reputation: 123

Why is my sum for two columns in psql incorrect?

I am trying to get the sum of two columns in psql

    select id as id, name as name, count(winner_id) as winner_count,
 (count(winner_id) +count(loser_id)) as total_matches from player left join 
matches on matches.winner_id = player.id group by winner_id, player.name,player.id, 
matches.loser_id;")

The total matches count should be the total or winner_id and loser_id in the matches table.

But it's incorrect.

It should return 1 match for all players, but instead it is returning 2 matches for the players who have won and 0 matches for the players who lost.

Upvotes: 0

Views: 168

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

count() counts the number of times that a value is not NULL. So, the two count()s will probably return the same value. I suspect you want something like this:

select id, name,
       sum(case when id = winner_id then 1 else 0 end) as winner_count,
       count(m.id) as total_matches
from player p left join 
     matches m
     on m.winner_id = p.id
group by p.name, p.id;

Upvotes: 1

Related Questions