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