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 <[EMAIL PROTECTED]> >Sent: Jul 31, 2008 9:41 AM >To: Fish Kungfu <[EMAIL PROTECTED]> >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 <[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 >>> >> >> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
