mysql query count not returning all data

Question

with the following query,

select users.name, race_results.raceid, race_results.place, race_results.winnings
from users
inner join race_results
where race_results.userid = users.id
order by race_results.place asc

I get

**********************************
name  | raceid | place | winnings
Bob   | 1      | 1     | 1000
John  | 3      | 1     | 1000
Bob   | 4      | 1     | 1000
Bob   | 2      | 1     | 1000
Bob   | 5      | 1     | 1000
John  | 5      | 2     | 500
Sarah | 3      | 2     | 500
Sarah | 2      | 2     | 500
Sarah | 5      | 3     | 250
John  | 2      | 3     | 250
Bob   | 3      | 5     | 50
John  | 1      | 5     | 50
John  | 4      | 5     | 50
Sarah | 1      | 6     | 50
Sarah | 4      | 6     | 50

with the following query,

select users.name, race_results.raceid, race_results.place, race_results.winnings
from users
inner join race_results
where race_results.userid = users.id
and race_results.place = 1
order by race_results.place asc

I get

*********************************
name | raceid | place | winnings
Bob  | 1      | 1     | 1000
Bob  | 2      | 1     | 1000
John | 3      | 1     | 1000
Bob  | 4      | 1     | 1000
Bob  | 5      | 1     | 1000

In the game, a user has won if they got first place.

I've tried something like this

select users.name, sum(race_results.winnings) as total_winnings,
       count(CASE WHEN race_results.place=1 THEN 1 ELSE 0 END) AS times_won_first_place
from users
inner join race_results
where race_results.userid = users.id
and race_results.place = 1
group by users.id
order by total_winnings desc

I get

**********************************************
name | total_winnings | times_won_first_place
Bob  | 4000           | 4
John | 1000           | 1

which is great, but what if this query wasn't primarily for getting how many times a user has one first place and it was just secondary information?

I want something like this

**********************************************
name  | total_winnings | times_won_first_place
Bob   | 4000           | 4
Sarah | 1350           | 0
John  | 1000           | 1

http://sqlfiddle.com/#!9/cb66d/8

Thanks.

Mikey · Accepted Answer · 2015-07-09 14:54:07Z

1

SELECT
  name
, SUM(winnings) as total_winnings
, SUM(IF(rr.place = 1, 1, 0)) as times_won_first_place
FROM Race_Results rr
JOIN Users u ON (rr.userid = u.id)
GROUP BY userid
ORDER BY total_winnings DESC

The case statement is a bit overkill if there's only 2 outcomes. http://sqlfiddle.com/#!9/cb66d/40

edited Jul 9, 2015 at 14:54

answered Jul 8, 2015 at 7:28

Mikey

2,95134 silver badges37 bronze badges

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

Comments

Abhay Chauhan · Accepted Answer · 2015-07-08 07:28:01Z

0

Here you go:

select users.name, sum(race_results.winnings) as total_winnings,
sum(CASE WHEN race_results.place=1 THEN 1 ELSE 0 END) AS
times_won_first_place from users 
left join race_results
on race_results.userid = users.id
group by users.id order by total_winnings desc

answered Jul 8, 2015 at 7:28

Abhay Chauhan

4143 silver badges11 bronze badges

Comments

Giorgi Nakeuri · Accepted Answer · 2015-07-08 07:32:51Z

0

Here is what you need. I think you have some error in your test output:

select u.name, 
       sum(r.winnings) as total_winnings,
       sum(case when r.place = 1 then 1 else 0 end) as times_won_first_place 
from Users u
left join Race_Results r on u.id = r.userid
group by u.id, u.name

Output:

name    total_winnings    times_won_first_place
Bob     4050              4
John    1850              1
Sarah   1350              0

Fiddle http://sqlfiddle.com/#!9/cb66d/34

answered Jul 8, 2015 at 7:32

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Collectives™ on Stack Overflow

mysql query count not returning all data

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related