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!
Kenni LarsenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
PortletPaulfreelancerCommented:
select count(distinct players.id)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Kenni LarsenAuthor Commented:
Returns:

4000
1
1
1

I need it to be:

4000
3
0
PortletPaulfreelancerCommented:
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
PortletPaulfreelancerCommented:
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
PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kenni LarsenAuthor Commented:
Works like a charm, thanks!
0
PortletPaulfreelancerCommented:
thanks, ( but I should have read the whole query at the start :(
cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.