Counting rows from a subquery

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

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

Tags:

Mysql

Select

Mysql 5.5

Recent Posts