Counting rows from a subquery

Question

How could I count rows from a SELECT query as a value? Such as

SELECT FUCNTIONIMLOOKINGFOR(SELECT * FROM anothertable) AS count FROM table;

So that count is an integer of how many rows the subquery SELECT * FROM anothertable returns.

EDIT

SELECT p.PostPID, p.PostUID, p.PostText, p.PostTime, u.UserUID, u.UserName, u.UserImage, u.UserRep,
    (
        SELECT COUNT(f.FlagTime)
            FROM Flags as f 
                JOIN Posts as p 
                ON p.PostPID = f.FlagPID
    ) as PostFlags
    FROM Posts AS p
        JOIN Users AS u
        ON p.PostUID = u.UserUID
    ORDER BY PostTime DESC
    LIMIT 0, 30

@Charlie - not sure if you're getting an error, but are you missing a comma before the ( SELECT ... ) as PostFlags? — Brett, Mar 18 '11 at 15:41
@Charlie - I'm not sure what the 'v' in v.FlagTime is referring to, you don't have any join labeled with a v, what about f.FlagTime? — Brett, Mar 18 '11 at 16:00

Kevin Peno · Accepted Answer · 2011-03-18T15:44:59.897

21

SELECT ( SELECT COUNT(id) FROM aTable ) as count FROM table

I assume your example is a truncated version of your actual query, so perhaps you should post what you are after to get a, possibly, more optimal query.

EDIT

Working directly from my brain, something like this should be more optimal.

SELECT p.PostPID, p.PostUID, p.PostText, p.PostTime, u.UserUID, u.UserName, u.UserImage, u.UserRep, COUNT(v.FlagTime) as postFlags
    FROM Flags as f 
    JOIN Posts as p ON p.PostPID = f.FlagPID
    JOIN Users AS u ON p.PostUID = u.UserUID
LIMIT 0, 30
GROUP BY p.PostPID
ORDER BY PostTime DESC

edited Mar 18 '11 at 15:44

answered Mar 18 '11 at 15:35

Kevin Peno

9,107
1
33
56

2

If not all posts have flags this would exclude those that do not. If that is the case then do `from posts left join Flags...` – Clodoaldo Neto Mar 18 '11 at 16:36

score 11 · Answer 2 · answered Mar 18 '11 at 15:37

11

You can say

SELECT COUNT(*) FROM anothertable

which will return a numeric value, which you can use in another query, such as in the select list of another query, or as a condition in another query.

SELECT someVariable FROM table
WHERE (SELECT COUNT(*) FROM anotherTable) > 5

OR

SELECT someVariable, (SELECT COUNT(*) FROM anotherTable) as count FROM table

answered Mar 18 '11 at 15:37

Brett

4,051
2
26
39

I updated my post. But the subquery seems to be executed without the join. – Shoe Mar 18 '11 at 15:49
1

Thank you! the second example was helpful – Chris Bier Aug 10 '14 at 02:00

Counting rows from a subquery

2 Answers2

Linked