[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

Count number of users with more than X played games

Hi Experts

The following SQL query returns a list of users (alias/username) who have played more than 4,000 games in our game. Instead, I would like to know how many users it is in total.

SELECT players.alias AS "4000"
FROM players, games WHERE games.opponent_id = players.id OR games.player_id = players.id AND players.id NOT IN (SELECT bots.profile_id FROM bots)
GROUP BY players.id
HAVING COUNT(games.id) >= 4000

Open in new window


If I change the query so that it makes a count, I get the number of games that each user has played instead of the number of users who have played over 4,000 games.

SELECT COUNT(players.id) AS "4000"
FROM players, games WHERE games.opponent_id = players.id OR games.player_id = players.id AND players.id NOT IN (SELECT bots.profile_id FROM bots)
GROUP BY players.id
HAVING COUNT(games.id) >= 4000

Open in new window


Can you please help me write the correct query?
Thanks in advance!
0
Kenni Larsen
Asked:
Kenni Larsen
  • 5
  • 4
1 Solution
 
IrogSintaCommented:
Try this:
SELECT COUNT(players.id) AS "4000"
FROM players, games WHERE games.opponent_id = players.id OR games.player_id = players.id AND players.id NOT IN (SELECT bots.profile_id FROM bots)
HAVING COUNT(games.id) >= 4000

Open in new window

Ron
0
 
Kenni LarsenAuthor Commented:
Output attached. Output
I already tried that but there are only 3 users who have played more than 4,000 games.
0
 
PortletPaulCommented:
select count(distinct players.id)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Kenni LarsenAuthor Commented:
Returns:

4000
1
1
1

I need it to be:

4000
3
0
 
PortletPaulCommented:
not surprising:

GROUP BY
      players.id

remove that
0
 
Kenni LarsenAuthor Commented:
SELECT COUNT(DISTINCT players.id) AS "4000"
FROM players, games WHERE games.opponent_id = players.id OR games.player_id = players.id AND players.id NOT IN (SELECT bots.profile_id FROM bots)
HAVING COUNT(games.id) >= 4000

Open in new window


Returns:
313802
0
 
PortletPaulCommented:
SELECT
      SUM(player_count) AS "4000"
FROM (
            SELECT
                  COUNT(DISTINCT players.id) AS player_count
            FROM players
               , games
            WHERE games.opponent_id = players.id
                  OR games.player_id = players.id
                  AND players.id NOT IN (
                        SELECT
                              bots.profile_id
                        FROM bots
                  )
            GROUP BY
                  players.id
            HAVING COUNT(games.id) >= 4000
      ) x

Open in new window

0
 
PortletPaulCommented:
this is more sensible
SELECT
      count(*) AS "4000"
FROM (
            SELECT
                  players.id
            FROM players
               , games
            WHERE games.opponent_id = players.id
                  OR games.player_id = players.id
                  AND players.id NOT IN (
                        SELECT
                              bots.profile_id
                        FROM bots
                  )
            GROUP BY
                  players.id
            HAVING COUNT(games.id) >= 4000
      ) x

Open in new window

0
 
Kenni LarsenAuthor Commented:
Works like a charm, thanks!
0
 
PortletPaulCommented:
thanks, ( but I should have read the whole query at the start :(
cheers, Paul
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now