COUNT en una consulta con múltiples UNIONES y un GRUPO POR CLÁUSULA

Estoy trabajando en una database que contiene 3 tablas:

  1. Una list de empresas
  2. Una tabla de los productos que venden
  3. Una tabla de precios que ofrecieron en cada date

Estoy haciendo una consulta como esta en mi php para generar una list de las compañías que ofrecen los precios más bajos en un determinado tipo de producto en una date determinada.

SELECT a.name AS company, c.id, MIN(c.price) AS apy FROM `companies` a JOIN `company_products` b ON b.company_id = a.id JOIN `product_prices` c ON c.product_id = b.id WHERE b.type = "%s" AND c.date = "%s" GROUP BY a.id ORDER BY c.price ASC LIMIT %d, %d 

Esto me da los datos que necesito, pero para implementar un buscapersonas en PHP necesito saber cuántas empresas ofrecen ese producto en ese día hay en total. El LIMIT significa que solo veo los primeros …

Traté de cambiar la cláusula SELECT para SELECCIONAR COUNT (a.id) o SELECT COUNT (DISTINCT (a.id)), pero ninguno de ellos parece darme lo que quiero. Intenté eliminar GROUP BY y ORDER BY en mi consulta de conteo, pero tampoco funcionó. ¿Algunas ideas?

Me parece que deberías GROUP BY a.id, c.id – agrupar por a.id solo significa que normalmente tendrás varios c.id s por a.id y solo obtendrás un "random-ish" " uno de ellos. Esto parece una cuestión de corrección básica. Una vez que haya solucionado eso, un SELECT COUNT(*) FROM inicial SELECT COUNT(*) FROM etc., etc., definitivamente le dará el número de filas que devolverá la siguiente consulta, por lo que puede preparar su buscapersonas según corresponda.

Este website sugiere que MySQL tiene un truco especial para esto, al less a partir de la versión 4:

Afortunadamente, desde MySQL 4.0.0 puede utilizar la opción SQL_CALC_FOUND_ROWS en su consulta, que le indicará a MySQL que cuente el número total de filas sin tener en count la cláusula LIMIT. Aún necesita ejecutar una segunda consulta para recuperar el recuento de filas, pero es una consulta simple y no tan compleja como su consulta que recuperó los datos.

El uso es bastante simple. En su consulta principal debe agregar la opción SQL_CALC_FOUND_ROWS justo después de SELECT y en la segunda consulta debe usar la function FOUND_ROWS () para get el número total de filas. Las consultas se verían así:

 SELECT SQL_CALC_FOUND_ROWS name, email FROM users WHERE name LIKE 'a%' LIMIT 10; SELECT FOUND_ROWS(); 

La única limitación es que debe llamar a la segunda consulta inmediatamente después de la primera porque SQL_CALC_FOUND_ROWS no guarda el número de filas en ningún lugar.