mysql COUNT row results

------=_NextPart_000_0059_01CC3142.EBDDDC90
Content-Type: text/plain;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable


Is there a way that=20

SELECT COUNT(auto_increment) as total_subscribers , `email` FROM =
`table`=20

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


The Verse of the Day
â€=9CEncouragement from Godâ€=99s Wordâ€=9D
http://www.TheVerseOfTheDay.info =20

------=_NextPart_000_0059_01CC3142.EBDDDC90--

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
geoff@gjctech.co.uk


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

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

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