Can you 'Count', but use GroupBY ??

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Can you 'Count', but use GroupBY ??

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Can you 'Count', but use GroupBY ??

Post 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");
(#10850)
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you 'Count', but use GroupBY ??

Post by simonmlewis »

It's the second query that's the problem tho. The first one works.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you 'Count', but use GroupBY ??

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Can you 'Count', but use GroupBY ??

Post by Christopher »

I don't think it will work if you do "COUNT(*) AS userid". Alias it "AS total_userids" or something.
(#10850)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Can you 'Count', but use GroupBY ??

Post by Weirdan »

Post Reply