Count rows on entire sql query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
trobale
Forum Newbie
Posts: 24
Joined: Mon Apr 23, 2007 11:42 am

Count rows on entire sql query

Post by trobale »

How do I count the rows with mysql (not with php throught mysql_num_rows) for the following sql query:

Code: Select all

SELECT SUM(Betting_Points + Other_Points) AS Sum_Points FROM teams,users WHERE teams.Team_ID=users.Team_ID AND teams.Team_ID!=1 GROUP BY teams.Team_ID HAVING Sum_Points > 0
The query works well and returns the sum points for each team (teams.Team_ID).

The problem is that I only want to know how many rows that query returns.

Thanks
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Use SELECT COUNT(*) as `count` instead
trobale
Forum Newbie
Posts: 24
Joined: Mon Apr 23, 2007 11:42 am

Post by trobale »

I have tried that, but it returns the COUNT(*) for each team (teams.Team_ID) since I have the group by clause.
I want the total COUNT(*) and not just the count for each team.

The problem is I have to use the group by in order to get the SUM value for each team.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Betting_Points + Other_Points
both fields of the table users?
Can there be negatives points or does the first Betting_Points!=0 or Other_Points!=0 mean "this team is in the result set"?
trobale
Forum Newbie
Posts: 24
Joined: Mon Apr 23, 2007 11:42 am

Post by trobale »

The actual query that should be cared about is

Code: Select all

SELECT COUNT(*) FROM teams,users WHERE teams.Team_ID=users.Team_ID GROUP BY teams.Team_ID HAVING SUM(Betting_Points + Other_Points) > $team_points
$team_points = the total points the selected team has (don't care about it...)

I have to tables, users and teams. Users have betting points and other points and they can be negative.

With the query above I try to count the number of teams that have more points than the selected team.

The problem is the group by thing. Now it returns an individual COUNT(*) for each team because of the GROUP BY clause. And without the GROUP BY clause I can't use the SUM function so that it returns each teams total SUM.

With the query above I can use mysql_num_rows() to get the actual count I want to get, but it takes too much resources if I have many teams and users in the database.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

IF your mysql version is recent enough to handle subqueries you can try

Code: Select all

SELECT count(*) FROM
(
  SELECT
    Sum(betting_points+other_points) as s
  FROM
    users
  GROUP BY
    team_id
  HAVING
    s > 9500
) AS foo
trobale
Forum Newbie
Posts: 24
Joined: Mon Apr 23, 2007 11:42 am

Post by trobale »

Thank You, problem solved!
Post Reply