CODEWITHSUNDEEP
sqlsql-servert-sql
Scott Selby
Scott Selby

Reputation: 9570

Get Count based on condition from SQL

I have a table , the columns that I am focused on are Id which is unique identifier, Value which is a decimal vote

I know how to group by Id and get a count of all rows having a Value of greater than 3. ( We're counting a Value > 3 as a Vote and a Value less than 2.9 to be -1 Vote) - moving from starts to thumbs up/down. anyways that doesn't matter . It was just to explain or rating system.

My question is : How can I do a query that

  select Id, Value,  count(*) as [COUNT]  
  from [Ratings]
  group by Id, Value 
  having Value > 3.0
  order by Id desc

I do the opposite to get down votes, how do I make in one single query to return all Id's and the number of votes ( if Value > 3 that would be 1 Vote if Value is less than 3 that would be -1 Vote)

Upvotes: 1

Views: 141

Answers (2)

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

SQL Server allows you to specify condition in aggregate functions.In your case, you need to use SUM with conditions..

So, this is how your final query looks like

  select Id, Value,SUM(CASE WHEN Value>3.0 THEN 1 ELSE -1 END) AS VoteCount 
  from [Ratings]
  group by Id 
  order by Id desc

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Just use conditional aggregation:

select Id,
       (sum(case when Value > 3.0 then 1 else 0 end) -
        sum(case when Value < 3.0 then 1 else 0 end) -- or maybe 2.9
       ) as TotalVotes
from [Ratings]
group by Id 
order by Id desc;

Alternatively, you could write:

select id, sum(case when Value > 3.0 then 1 else -1 end)

Upvotes: 3

Related Questions