[PHP-DB] Re: mysql COUNT row results
Hi Ron, On Thursday, June 23, 2011, 6:14:38 AM, you wrote: Is there a way that SELECT COUNT(auto_increment) as total_subscribers , `email` FROM `table` may exist within the same query and provide more than 1 row of search results? When I run a query like this the COUNT portion of the result is allowing only 1 to be selected. My desire is to have the the COUNT result appended to each row. Thoughts anyone? Ron As you noticed, the aggregate function COUNT() means that you get a table with only one row, so you need to join that table to the one that it aggregates. So something like: SELECT A.total_subscribers, B.email FROM table B, (SELECT COUNT(auto_increment) AS total_subscribers FROM table) AS A Should do the trick. HTH, -- Geoff Lane Cornwall, UK ge...@gjctech.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: mysql COUNT row results
-Original Message- From: Geoff Lane Sent: Thursday, June 23, 2011 2:15 AM To: Ron Piggott Cc: php-db@lists.php.net Subject: Re: mysql COUNT row results Hi Ron, On Thursday, June 23, 2011, 6:14:38 AM, you wrote: Is there a way that SELECT COUNT(auto_increment) as total_subscribers , `email` FROM `table` may exist within the same query and provide more than 1 row of search results? When I run a query like this the COUNT portion of the result is allowing only 1 to be selected. My desire is to have the the COUNT result appended to each row. Thoughts anyone? Ron As you noticed, the aggregate function COUNT() means that you get a table with only one row, so you need to join that table to the one that it aggregates. So something like: SELECT A.total_subscribers, B.email FROM table B, (SELECT COUNT(auto_increment) AS total_subscribers FROM table) AS A Should do the trick. HTH, Geoff thank you so much. This worked. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: mysql COUNT row results
If you're looking for a count of ALL records, why do you want to append that to EACH record? Now if you're looking for a count of subscribers who have certain attributes (ie, unique groups of subscribers), then you would use a group by clause and include the attributes that define the group in your selection. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
