CODEWITHSUNDEEP
sqlpostgresqlcount
Hugo Sousa
Hugo Sousa

Reputation: 1926

Show COUNT(*) column for value 0 in multiple rows

I have a table Content

Content(id, contentText, contentDate, idUser → User, contentType)

and a table VoteQuestion

VoteQuestion(idUser → User, idQuestion → Content, isUp)

I want to select, for instance, the first 30 most recent questions. 

SELECT * 
FROM "Content" 
WHERE "type" = 'QUESTION'
ORDER BY "contentDate"
LIMIT 30

However, I want to concatenate to this another columns with information related to this question, so I don't need to query the database again for each question returned.

For instance, I want to count the votes for each question, and get it all returned in the same row.

Example:

| id | contentText | contentDate | idUser | contentType | votes |
-----------------------------------------------------------------
| 2  |   'abc'     | '2013-03-25'|  192   |  'QUESTION' |  10   |

I tried the following query:

WITH question AS
(SELECT * 
FROM "Content" 
WHERE "type" = 'QUESTION'
ORDER BY "contentDate"
LIMIT 30
)

SELECT COUNT("VoteQuestion"."idUser") 
FROM "VoteQuestion", question
WHERE "idQuestion" = question."id"
GROUP BY question."id";

but this doesn't return the questions with number of votes = 0 (only 16 questions returned instead of 30). How to solve it?

Then, how can I concenate both tables? And how can I subtract the votes where isUp is true and isUp is false in the query?

Upvotes: 1

Views: 162

Answers (2)

user330315
user330315

Reputation:

You need an outer join for that:

WITH question AS (
  SELECT * 
  FROM "Content" 
  WHERE "type" = 'QUESTION'
  ORDER BY "contentDate"
  LIMIT 30
)
SELECT COUNT("VoteQuestion"."idUser") 
FROM question
  LEFT JOIN "VoteQuestion" ON "idQuestion" = question."id"
GROUP BY question."id";

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You can subselect the vote count:

SELECT Content.* , 
(
  SELECT COUNT(*) 
  FROM VoteQuestion
  WHERE idQuestion = Content.id
) as votes
FROM Content
WHERE type = 'QUESTION'
ORDER BY contentDate
LIMIT 30

Upvotes: 1

Related Questions