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!