Mysql query problem

Posted by Lost_in_code on Stack Overflow See other posts from Stack Overflow or by Lost_in_code
Published on 2010-05-30T12:52:19Z Indexed on 2010/05/30 13:02 UTC
Read the original article Hit count: 138

Filed under:
|

Below is a sample table:

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?

© Stack Overflow or respective owner

Related posts about sql

Related posts about mysql