Use "how" several times

Question

I have it:

SELECT
   posts.id,
   (SELECT COUNT(*) FROM votes WHERE votes.post = posts.id) AS votesCount,
   (SELECT SUM(vote) FROM votes WHERE votes.post = posts.id) AS votesUp
FROM posts WHERE posts.id = 1

How can I get the difference between voteCount and votesUp without making another SELECT? Sort of:

SELECT
   posts.id,
   (SELECT COUNT(*) FROM votes WHERE votes.post = posts.id) AS votesCount,
   (SELECT SUM(vote) FROM votes WHERE votes.post = posts.id) AS votesUp,
   votesCount - votesUp AS votesDown
FROM posts WHERE posts.id = 1

Is it possible, or do I need to call another SELECT? Thank.

By the way, sorry for my bad english.

Answer 1

I would suggest that you join the voices instead of using subqueries, then you can easily use aggregates:

select
  p.id,
  count(v.post) as votesCount,
  sum(v.vote) as votesUp,
  count(v.post) - sum(v.vote) as votesDown
from
  posts p
  left join votes v on v.post = p.id
where
  p.id = 1
group by
  p.id

Answer 2

Your subqueries are not entirely clear. Typically, you want to use subqueries as a last resort, preferring joins. For instance:

SELECT
    posts.id, COUNT(votes.*) AS votesCount, SUM(vote) AS votesUp 
FROM posts
    LEFT JOIN votes ON votes.post = posts.id
WHERE
    posts.id = 1
GROUP BY posts.id

, , , ( :/):

SELECT
    posts.id, COUNT(votes.*) AS votesCount, SUM(vote) AS votesUp,
    COUNT(votes.id) - SUM(vote) AS votesDown
FROM posts
    LEFT JOIN votes ON votes.post = posts.id
WHERE
    posts.id = 1
GROUP BY posts.id

Answer 3

SELECT?

    SELECT
    posts.id,
    (SELECT COUNT(*) FROM votes WHERE votes.post = posts.id) AS votesCount,
    (SELECT SUM(vote) FROM votes WHERE votes.post = posts.id) AS votesUp
    (SELECT SUM(CASE WHEN vote=0 THEN 1 ELSE 0 END) FROM votes WHERE votes.post = posts.id) AS votesDown
    FROM posts WHERE posts.id = 1