Re: [PHP-DB] Efficient way to count rows when using GROUP BY
It worked! SELECT COUNT(*) FROM (SELECT COUNT(*) FROM ad.off_enrol GROUP BY jcu_campus, sub_code) t1 Thank you list -:) Martín Marqués wrote: El Dom 26 Jun 2005 07:39, Ross Honniball escribió: Hi All, I use SELECT COUNT(*) FROM whatever WHERE whatever to determine how many rows a particular query will return (then use this count to assist with pagination). The problem is that this does not work if the query contains a GROUP BY statement as, of course, the query will return many rows each with a count by each grouping. Does anyone know an efficient way to determine the number of ROWS a query with a GROUP BY statement will return (without just executing the entire query with no LIMIT statement)? You are not stating which database you are working with, but I would do this (don't know if it's efficient, but it'll work): SELECT sum(count) FROM (SELECT COUNT(*) AS count FROM whatever WHERE whatever) t1; Hope it helps. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Efficient way to count rows when using GROUP BY
El Dom 26 Jun 2005 07:39, Ross Honniball escribió: > Hi All, > > I use > > SELECT COUNT(*) FROM whatever WHERE whatever > > to determine how many rows a particular query will return (then use this count to assist with pagination). > > The problem is that this does not work if the query contains a GROUP BY statement as, of course, > the query will return many rows each with a count by each grouping. > > Does anyone know an efficient way to determine the number of ROWS a query with a GROUP BY > statement will return (without just executing the entire query with no LIMIT statement)? You are not stating which database you are working with, but I would do this (don't know if it's efficient, but it'll work): SELECT sum(count) FROM (SELECT COUNT(*) AS count FROM whatever WHERE whatever) t1; Hope it helps. -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; - Martín Marqués | Programador, DBA Centro de Telemática| Administrador Universidad Nacional del Litoral - -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Efficient way to count rows when using GROUP BY
If you don't want a group count, but want a total row count instead, there's no reason to have the group by statement, If you're trying to have a conglomerate result, where one column is always equal to the total count, wouldn't it be more efficient to have a seperate query for that? -Micah > Hi All, > > I use > > SELECT COUNT(*) FROM whatever WHERE whatever > > to determine how many rows a particular query will return (then use this > count to assist with pagination). > > The problem is that this does not work if the query contains a GROUP BY > statement as, of course, the query will return many rows each with a count > by each grouping. > > Does anyone know an efficient way to determine the number of ROWS a query > with a GROUP BY statement will return (without just executing the entire > query with no LIMIT statement)? > > Thanks ... Ross -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
