Mysql query problem

Question

Below is an over-simplified version of table I'm using:

fruits
+-------+---------+ 
| id    | type    | 
+-------+---------+ 
| 1     | apple   | 
| 2     | orange  | 
| 3     | banana  | 
| 4     | apple   | 
| 5     | apple   | 
| 6     | apple   | 
| 7     | orange  | 
| 8     | apple   | 
| 9     | apple   | 
| 10    | banana  | 
+-------+---------+ 

Following are the two queries of interest:

SELECT * FROM fruits WHERE type='apple' LIMIT 2;

SELECT COUNT(*) AS total FROM fruits WHERE type='apple'; // output 6

I want to combine these two queries so that the results looks like this:

+-------+---------+---------+  
| id    | type    | total   |
+-------+---------+---------+  
| 1     | apple   | 6       |
| 4     | apple   | 6       | 
+-------+---------+---------+

The output has to be limited to 2 records but it should also contain the total number of records of the type apple.

How can this be done with 1 query?

Answer 1

Sounds like a UNION of the first two queries and select from the UNION with a LIMIT.

Answer 2

You should use SQL_CALC_FOUND_ROWS for that.

SELECT SQL_CALC_FOUND_ROWS * FROM fruits WHERE type='apple' LIMIT 2;

will return the IDs of your apples, and remember how much it would have returned without the LIMIT clause

SELECT FOUND_ROWS();

will return how many apples would have been found, without the limit statement.

Answer 3

SELECT *, (SELECT COUNT(*) AS total FROM fruits WHERE type='apple') AS Total 
FROM fruits WHERE type='apple' LIMIT 2;

Depending on how MySQL interprets it, it may cache the inner query so that it doesn't have to reevaluate it for every record.

Another way to do it is with a nested query and a join (this would be useful it you need more than one fruit type, for example):

SELECT fruits.*, counts.total
FROM fruits 
  INNER JOIN (SELECT type, COUNT(*) AS total FROM fruits GROUP BY type) counts ON (fruits.type = counts.type)
WHERE fruits.type='apple'
LIMIT 2;