Beautiful! That's exactly what I needed. Thanks, Roy
mysql> SELECT COUNT(*) FROM (SELECT aviName,MAX(dateTime) FROM aviTrackerMain WHERE
> DATE(dateTime) LIKE CONCAT(DATE(NOW()),'%') GROUP BY aviName) as T;
+----------+
| COUNT(*) |
+----------+
| 49 |
+----------+
1 row in set (0.30 sec)
Cheers....Fish
-----Original Message-----
>From: Roy Lyseng <Roy.Lyseng@stripped>
>Sent: Jul 31, 2008 9:41 AM
>To: Fish Kungfu <fish.kungfu@stripped>
>Subject: Re: COUNT returned rows of a SELECT
>
>Hi,
>
>generally you should be able to use the select query as a derived table,
>and select the row count from this:
>
>select count(*) from (select <your query>) as T;
>
>Note that the derived table always needs an alias (here T).
>
>Cheers,
>Roy
>
>Fish Kungfu wrote:
>> Thanks for trying guys, but that's still not quite what I'm looking
>> for. All I really want is the total number of rows returned for the
>> query result.
>>
>> For example, my the SELECT that Ananda suggested returns this:
>>
>> mysql> SELECT aviName,MAX(dateTime) ,count(*) FROM aviTrackerMain WHERE
>> DATE(dateTime) LIKE CONCAT(DATE(NOW()),'%') GROUP BY aviName;
>> +-------------------+---------------------+----------+
>> | aviName | MAX(dateTime) | count(*) |
>> +-------------------+---------------------+----------+
>> | user1 | 2008-07-31 02:28:42 | 6 |
>> | user2 | 2008-07-31 04:56:43 | 4 |
>> | user3 | 2008-07-31 06:54:44 | 2 |
>> | user4 | 2008-07-31 03:10:43 | 1 |
>> | user5 | 2008-07-31 07:02:44 | 67 |
>> | user6 | 2008-07-31 00:42:42 | 1 |
>> | user7 | 2008-07-31 01:02:42 | 10 |
>> | user8 | 2008-07-31 00:22:41 | 22 |
>> | user9 | 2008-07-31 00:22:42 | 22 |
>> | user10 | 2008-07-31 05:16:44 | 16 |
>> | user11 | 2008-07-31 05:15:44 | 1 |
>> +-------------------+---------------------+----------+
>> 11 rows in set (0.11 sec)
>>
>> What I'd like to have returned is, "11", the final total number of rows
>> that the query returned, not the count of each user occurance per GROUPing.
>>
>> Ideally, I was hoping COUNT() could work like this, BUT it doesn't of
>> course:
>>
>> mysql> SELECT COUNT(SELECT aviName,MAX(dateTime) ,count(*) FROM
>> aviTrackerMain WHERE DATE(dateTime) LIKE CONCAT(DATE(NOW()),'%') GROUP
>> BY aviName);
>>
> +--------------------------------------------------------------------------------------------------------------------------------------+
>> | COUNT(SELECT aviName,MAX(dateTime) ,count(*) FROM aviTrackerMain WHERE
>> DATE(dateTime) LIKE CONCAT(DATE(NOW()),'%') GROUP BY aviName) |
>>
> +--------------------------------------------------------------------------------------------------------------------------------------+
>> |
>
>> 11|
>>
> +--------------------------------------------------------------------------------------------------------------------------------------+
>>
>>
>>
>>
>> Rob Wultsch wrote:
>>> On Wed, Jul 30, 2008 at 9:41 PM, Fish Kungfu
> <fish.kungfu@stripped> wrote:
>>>> Using MySQL commands only (not PHP's mysql_num_rows), is there a way to
>>>> COUNT the number of rows returned from a SELECT.....GROUP BY?
>>>>
>>>> My primary SELECT query is this:
>>>>
>>>> SELECT aviName,MAX(dateTime) FROM aviTrackerMain WHERE DATE(dateTime)
>>>> LIKE CONCAT(DATE(NOW()),'%') GROUP BY aviName;
>>>>
>>>> And it faithfully returns x-number of rows. However, I want to be able
>>>> to capture the number of rows it returns. If I have to I will use PHP,
>>>> but I was hoping for a way to do it with just MySQL.
>>>>
>>>>
>>>> Thanks very much in advance......Fish
>>> FOUND_ROWS() might be a solution that works for you. Take a look at:
>>>
> http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
>>>
>>
>>
