Page 1 of 1

Count rows on entire sql query

Posted: Thu Jul 19, 2007 4:06 pm
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

Posted: Thu Jul 19, 2007 4:34 pm
by John Cartwright
Use SELECT COUNT(*) as `count` instead

Posted: Fri Jul 20, 2007 4:04 am
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.

Posted: Fri Jul 20, 2007 5:56 am
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"?

Posted: Fri Jul 20, 2007 6:14 am
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.

Posted: Fri Jul 20, 2007 7:12 am
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

Posted: Sat Jul 21, 2007 3:38 am
by trobale
Thank You, problem solved!