Mysql query: combine two queries

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?

This is a trimmed down version of a table I'm using which will have more than a million records, and it's missing 5 other columns. I put this table for simplicity's sake. Right now I'm using 2 queries but wondered how this can be achieved with 1 query (for learning purpose) — Yeti
– Yeti, Commented May 30, 2010 at 13:00
Looks like you oversimplified it and it may mislead ones who will try to answer. Personally I prefer sensible questions. — Your Common Sense
– Your Common Sense, Commented May 30, 2010 at 13:03

Senseful · Accepted Answer · 2010-05-30 13:11:01Z

2

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;

edited May 30, 2010 at 13:11

answered May 30, 2010 at 13:05

Senseful

92.7k73 gold badges339 silver badges497 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Your Common Sense Over a year ago

Hey @Lost, aren't your current 2 queries way simpler than this monster? ;)

Yeti Over a year ago

@col: Well maybe. But I was curious to learn to do this with 1 query. And yes, though it may not look pretty, I prefer to use 1 query instead of 2.

Your Common Sense Over a year ago

@Lost and you'll be wrong. There is not a single benefit in doing it with one.

Senseful Over a year ago

@col: hah, this query is a cake walk... take a look at one of these beasts I recently wrote: stackoverflow.com/questions/2938427/… stackoverflow.com/questions/2776632/…

Konerak · Accepted Answer · 2010-05-30 12:58:41Z

1

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.

answered May 30, 2010 at 12:58

Konerak

39.8k13 gold badges102 silver badges121 bronze badges

1 Comment

Konerak Over a year ago

You should put the SQL_CALC_FOUND_ROWS in conditional-comments though, to allow graceful failing on SQL-servers that don't support it.

Collectives™ on Stack Overflow

Mysql query: combine two queries

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related