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 <[EMAIL PROTECTED]> 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
>
