Re: COUNT returned rows of a SELECT
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 -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COUNT returned rows of a SELECT
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
RE: COUNT returned rows of a SELECT
-Original Message- From: Fish Kungfu [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2008 12:41 AM To: mysql@lists.mysql.com Subject: COUNT returned rows of a SELECT 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; [JS] SELECT SQL_CALC_FOUND_ROWS aviName,MAX(dateTime) FROM aviTrackerMain WHERE DATE(dateTime) LIKE CONCAT(DATE(NOW()),'%') GROUP BY aviName; SELECT FOUND_ROWS(); 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COUNT returned rows of a SELECT
On Thu, Jul 31, 2008 at 8:00 AM, Fish Kungfu [EMAIL PROTECTED] wrote: 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); SELECT COUNT(DISTINCT aviName); I don't really understand why you don't want to just look at the number of rows you get from the first query though. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COUNT returned rows of a SELECT
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) CheersFish -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]
COUNT returned rows of a SELECT
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COUNT returned rows of a SELECT
SELECT aviName,MAX(dateTime) ,count(*) FROM aviTrackerMain WHERE DATE(dateTime) LIKE CONCAT(DATE(NOW()),'%') GROUP BY aviName; This will also give you count of rows On 7/31/08, 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
