Clever Geek Handbook

Votes Requests for Top Positions Based on Percentage

I have a table of votes that looks like this:

id, type, scope, region, UserId, ItemId, updatedAt, createdAt

I am trying to sum the top positions by percentages based on votes / total votes.

The last part is the proposal WHERE

for votes createdAt

between two timestamps.

I feel like this is a very normal thing for querying statistics. But I'm not sure how to do this.

The closest I got was:

SELECT "Votes"."ItemId", count("Votes"."ItemId") tots, count("Votes"."type" = 'up') AS yes, count("Votes"."type" = 'down') AS NO
FROM "Votes"
WHERE "Votes"."ItemId" IN (
    SELECT "Votes"."ItemId" FROM "Votes"
)
GROUP BY "Votes"."ItemId"

It's a long way from what it takes. So I would like to help here. It was very difficult to find good sql resources on things like this.

+3


source to share


2 answers


You can use operator CASE

to use 1 for each vote and -1 for down vote and get the sum of them



SELECT ItemId, SUM(CASE [type] WHEN 'up' THEN 1 WHEN 'down' THEN -1 END)
FROM Votes
WHERE createdAt >= 'startTime'
AND createdAt <= 'endTime'
GROUP BY ItemId
+2


source


You can do it with conditional aggregation:

select itemid, 
       sum(case when type = 'up' then 1 else 0 end) up,
       sum(case when type = 'down' then 1 else 0 end) down,
       sum(1) total,
       100.0*sum(case when type = 'up' then 1 else 0 end)/sum(1) upperc,
       100.0*sum(case when type = 'down' then 1 else 0 end)/sum(1) downperc
from votes
group by itemid


Fiddle http://sqlfiddle.com/#!15/1337d/6

+2


source






More articles:


All Articles