/    Sign up×
Community /Pin to ProfileBookmark

Count Query Help

Copy linkTweet thisAlerts:
Dec 07.2021

Morning Folks,

A tricky query if someone may help.

I’ve got two tables which I’m needing to do some basic math on. First one is a game table, second one is a player table.

It’s a weird format and it’s not my table structure. In each game row I have player1Id and player2Id.

I need to count all games and find out who had the most games. Sometimes a player will be player1 sometimes they’ll be player2.

I had tried something like this but it’s not working

[code]
SELECT COUNT(g.gameId) as TTL, p.playerId FROM `games` g
JOIN players p ON p.playerId = g.player1Id OR g.player2Id

GROUP BY p.playerId

order by COUNT(g.gameId) desc
[/code]

to post a comment
PHP

1 Comments(s) ↴

Copy linkTweet thisAlerts:
@ginerjmDec 07.2021 — How about reversing your view in the query? Try selecting each player and joining the games that he has played to get your count
<i>
</i>select p.playerid, count(g.gameid) as games from players p, games g
where p.playerid = g.player1id or p.playerid = g.player2id
group by p.playerid
order by games desc

Not sure about my query syntax but you get the point
×

Success!

Help @singaporeDude spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 4.25,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,
)...