COUNT in a query with multiple JOINS and a GROUP BY CLAUSE

Question

I am working on a database that contains 3 tables:

1. A list of companies
2. A table of the products they sell
3. A table of prices they offered on each date

I'm doing a query like this in my php to generate a list of the companies offering the lowest prices on a certain product type on a certain date.

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

This gets me the data I need, but in order to implement a pager in PHP I need to know how many companies offering that product on that day there are in total. The LIMIT means that I only see the first few...

I tried changing the SELECT clause to SELECT COUNT(a.id) or SELECT COUNT(DISTINCT(a.id)) but neither of those seem to give me what I want. I tried removing the GROUP BY and ORDER BY in my count query, but that didn't work either. Any ideas?

Answer 1

Looks to me like you should GROUP BY a.id, c.id -- grouping by a.id only means you'll typically have several c.ids per a.id, and you're just getting a "random-ish" one of them. This seems like a question of basic correctness. Once you have fixed that, an initial SELECT COUNT(*) FROM etc etc should then definitely give you the number of rows the following query will return, so you can prepare your pager accordingly.

Answer 2

This website suggests MySQL has a special trick for this, at least as of version 4:

Luckily since MySQL 4.0.0 you can use SQL_CALC_FOUND_ROWS option in your query which will tell MySQL to count total number of rows disregarding LIMIT clause. You still need to execute a second query in order to retrieve row count, but it’s a simple query and not as complex as your query which retrieved the data.

Usage is pretty simple. In you main query you need to add SQL_CALC_FOUND_ROWS option just after SELECT and in second query you need to use FOUND_ROWS() function to get total number of rows. Queries would look like this:

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

SELECT FOUND_ROWS();

The only limitation is that you must call second query immediately after the first one because SQL_CALC_FOUND_ROWS does not save number of rows anywhere.