List:General Discussion« Previous MessageNext Message »
From:Fish Kungfu Date:July 31 2008 4:55pm
Subject:Re: COUNT returned rows of a SELECT
View as plain text  
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
>>>
>> 
>> 

Thread
COUNT returned rows of a SELECTFish Kungfu31 Jul
  • Re: COUNT returned rows of a SELECTAnanda Kumar31 Jul
  • Re: COUNT returned rows of a SELECTRob Wultsch31 Jul
    • Re: COUNT returned rows of a SELECTFish Kungfu31 Jul
      • Re: COUNT returned rows of a SELECTPerrin Harkins31 Jul
  • RE: COUNT returned rows of a SELECTJerry Schwartz31 Jul
Re: COUNT returned rows of a SELECTFish Kungfu31 Jul