Page 1 of 1
Can you 'Count', but use GroupBY ??
Posted: Mon Feb 11, 2013 8:27 am
by simonmlewis
Code: Select all
$result = mysql_query ("SELECT * FROM samples WHERE (status = 'closed') GROUP BY userid ORDER BY dateadded ASC LIMIT $offset, $rowsPerPage");
----
SELECT COUNT(id) AS numrows FROM samples WHERE (status = 'closed')
I have a page that lists requests from people. The top query finds each 'group' of requests individuals, but because there are so many, I need to put it into pages. So, the second query is part of the page number bit - but it won't work, because as it stands, it will show 2017 rows - when 'grouped' there aren't that many, because 10 requests from one person, shows as 1 in the upper query.
So I need to get the full num rows, from the top count.
How do I do that, in the second query?
Re: Can you 'Count', but use GroupBY ??
Posted: Mon Feb 11, 2013 12:41 pm
by Christopher
Did you try:
Code: Select all
$result = mysql_query ("SELECT *,COUNT(*) as total_userids FROM samples WHERE (status = 'closed') GROUP BY userid ORDER BY dateadded ASC LIMIT $offset, $rowsPerPage");
Re: Can you 'Count', but use GroupBY ??
Posted: Mon Feb 11, 2013 12:50 pm
by simonmlewis
It's the second query that's the problem tho. The first one works.
Re: Can you 'Count', but use GroupBY ??
Posted: Mon Feb 11, 2013 12:59 pm
by simonmlewis
$query = "SELECT *,COUNT(*) as userid FROM samples WHERE (status = 'closed') GROUP BY userid";
For the second of the two scripts, this produces nothing.
Re: Can you 'Count', but use GroupBY ??
Posted: Mon Feb 11, 2013 4:45 pm
by Christopher
I don't think it will work if you do "COUNT(*) AS userid". Alias it "AS total_userids" or something.
Re: Can you 'Count', but use GroupBY ??
Posted: Mon Feb 11, 2013 8:05 pm
by Weirdan