CODEWITHSUNDEEP
mysql
Jack
Jack

Reputation: 9784

COUNTing total rows, whilst simultaneously SELECTing a subset of these - in one MySQL query?

I am pulling through the latest five entries in a database. I also want to show the total number of entries beside this.

Currently I'm running two queries - the first, to get the latest five:

SELECT reference.id, reference.name
FROM reference
WHERE (status = 2 OR status = 3)
ORDER BY reference.date
LIMIT 5

The second, to count the total:

SELECT COUNT(reference.status) AS complete_count
FROM reference
WHERE (status = 2 OR status = 3)

Is there any way to join these two into one? When I try and add another column to the first query's output (the COUNT), it only returns one row.

Thanks!

Upvotes: 1

Views: 935

Answers (3)

Adam Wenger
Adam Wenger

Reputation: 17540

This should give you what you're hoping for:

SELECT reference.id, reference.name, COUNT(1) AS complete_count
FROM reference
WHERE status = 2
   OR status = 3
GROUP BY reference.id, reference.name
ORDER BY reference.date
LIMIT 5

Upvotes: 3

ajreal
ajreal

Reputation: 47321

Yes, few ways :-

select reference.id, reference.name, b.complete_count
FROM reference
JOIN
(
 SELECT COUNT(reference.status) AS complete_count
 FROM reference
 WHERE (status = 2 OR status = 3)
) as b
WHERE ...

This method is better as it do a count, then JOIN.

The another answer proposed is going to trigger the subquery for each row,
which is less ideal.

Or you can use http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows (two queries)

Upvotes: 0

Yevgeny Simkin
Yevgeny Simkin

Reputation: 28349

you can pull a sub select as the item so...

select foo, bar, (select count(*) from tableA) from tableA;

Keep in mind that depending on the complexity of the rest of the query this may be a fairly expensive select though.

Upvotes: 0

Related Questions