Mysql query top 10 soccer scorers with one query
up vote
0
down vote
favorite
I have problem to making soccer/football top 10 scorers.
I have something like this:
SQLFiddle
SELECT p.*, count(*) as num_goals
FROM match_goals g
INNER JOIN match_players p ON g.match_player_id = p.id
INNER JOIN matches m ON m.id = p.match_id
WHERE p.is_deleted = 0 AND
g.is_own_goal = 0 AND
m.season_id = 2
GROUP BY p.id
ORDER BY num_goals DESC
LIMIT 10
I have same player scored different games goals.
It doesnt sum.. shows 6 goals and 2 goals.. But it is same player.. and in same team..
mysql mysqli
asked 2 days ago
James T.
6
New contributor
James T. is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
0
down vote
favorite
I have problem to making soccer/football top 10 scorers.
I have something like this:
SQLFiddle
SELECT p.*, count(*) as num_goals
FROM match_goals g
INNER JOIN match_players p ON g.match_player_id = p.id
INNER JOIN matches m ON m.id = p.match_id
WHERE p.is_deleted = 0 AND
g.is_own_goal = 0 AND
m.season_id = 2
GROUP BY p.id
ORDER BY num_goals DESC
LIMIT 10
I have same player scored different games goals.
It doesnt sum.. shows 6 goals and 2 goals.. But it is same player.. and in same team..
mysql mysqli
asked 2 days ago
James T.
6
New contributor
James T. is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
My brain is shutting down at the end of the day - but if I just run select * from your above query using the same constraints it only yields 1 player, who scored 8 goals in 2 matches - I don't know if that's from your dummy data or maybe only one player scored for that season.
– Hatt
2 days ago
2
The data set isn't representative of the problem
– Strawberry
2 days ago
Please provide minimal yet complete sample data which is able to cover all the cases (including edgecases).
– Madhur Bhaiya
yesterday
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have problem to making soccer/football top 10 scorers.
I have something like this:
SQLFiddle
SELECT p.*, count(*) as num_goals
FROM match_goals g
INNER JOIN match_players p ON g.match_player_id = p.id
INNER JOIN matches m ON m.id = p.match_id
WHERE p.is_deleted = 0 AND
g.is_own_goal = 0 AND
m.season_id = 2
GROUP BY p.id
ORDER BY num_goals DESC
LIMIT 10
I have same player scored different games goals.
It doesnt sum.. shows 6 goals and 2 goals.. But it is same player.. and in same team..
mysql mysqli
asked 2 days ago
James T.
6
New contributor
James T. is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have problem to making soccer/football top 10 scorers.
I have something like this:
SQLFiddle
SELECT p.*, count(*) as num_goals
FROM match_goals g
INNER JOIN match_players p ON g.match_player_id = p.id
INNER JOIN matches m ON m.id = p.match_id
WHERE p.is_deleted = 0 AND
g.is_own_goal = 0 AND
m.season_id = 2
GROUP BY p.id
ORDER BY num_goals DESC
LIMIT 10
I have same player scored different games goals.
It doesnt sum.. shows 6 goals and 2 goals.. But it is same player.. and in same team..
mysql mysqli
mysql mysqli
asked 2 days ago
James T.
6
New contributor
James T. is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 days ago
James T.
6
New contributor
James T. is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 days ago
James T.
6
New contributor
James T. is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 days ago
James T.
6
asked 2 days ago
James T.
6
6
New contributor
James T. is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
James T. is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
James T. is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
My brain is shutting down at the end of the day - but if I just run select * from your above query using the same constraints it only yields 1 player, who scored 8 goals in 2 matches - I don't know if that's from your dummy data or maybe only one player scored for that season.
– Hatt
2 days ago
2
The data set isn't representative of the problem
– Strawberry
2 days ago
Please provide minimal yet complete sample data which is able to cover all the cases (including edgecases).
– Madhur Bhaiya
yesterday
add a comment |
My brain is shutting down at the end of the day - but if I just run select * from your above query using the same constraints it only yields 1 player, who scored 8 goals in 2 matches - I don't know if that's from your dummy data or maybe only one player scored for that season.
– Hatt
2 days ago
2
The data set isn't representative of the problem
– Strawberry
2 days ago
Please provide minimal yet complete sample data which is able to cover all the cases (including edgecases).
– Madhur Bhaiya
yesterday
My brain is shutting down at the end of the day - but if I just run select * from your above query using the same constraints it only yields 1 player, who scored 8 goals in 2 matches - I don't know if that's from your dummy data or maybe only one player scored for that season.
– Hatt
2 days ago
My brain is shutting down at the end of the day - but if I just run select * from your above query using the same constraints it only yields 1 player, who scored 8 goals in 2 matches - I don't know if that's from your dummy data or maybe only one player scored for that season.
– Hatt
2 days ago
2
2
The data set isn't representative of the problem
– Strawberry
2 days ago
The data set isn't representative of the problem
– Strawberry
2 days ago
Please provide minimal yet complete sample data which is able to cover all the cases (including edgecases).
– Madhur Bhaiya
yesterday
Please provide minimal yet complete sample data which is able to cover all the cases (including edgecases).
– Madhur Bhaiya
yesterday
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
James T. is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
James T. is a new contributor. Be nice, and check out our Code of Conduct.
James T. is a new contributor. Be nice, and check out our Code of Conduct.
James T. is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53402446%2fmysql-query-top-10-soccer-scorers-with-one-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
My brain is shutting down at the end of the day - but if I just run select * from your above query using the same constraints it only yields 1 player, who scored 8 goals in 2 matches - I don't know if that's from your dummy data or maybe only one player scored for that season.
– Hatt
2 days ago
2
The data set isn't representative of the problem
– Strawberry
2 days ago
Please provide minimal yet complete sample data which is able to cover all the cases (including edgecases).
– Madhur Bhaiya
yesterday