1
I have a table with 3 columns id
, nome
and voto
. They’re populated with a lot of records, and I need a calculation to return the name with best "balance" of votes. The column voto
can only receive values as 'yes' and 'no', being of type string.
What I need to do is check the amount of 'yes' and 'no' of all the records and return whoever has the biggest balance. If you have a yes, add a point, if not, remove a point. I’m not able to work with strings.
I am using SQL Server
In the above case I need you to return the name of Ann, because it has a better balance of points
OBS.: the ids are unique and the names can be repeated
As are these tables, the ids are unique and the names repeat, or id and names repeat?
– Reginaldo Rigo
I’ll put an image to clarify
– DiChrist
I was wrong in the question, it is not to return the id, but the name
– DiChrist
When you say 'balance' you mean the difference between Yes and No?
– Reginaldo Rigo
This. If the yes number is higher, it will give a positive balance, otherwise it will be negative or null when the yes and the no are equal
– DiChrist
Daniel’s answer is correct. Except for one detail:
SELECT nome, SUM(CASE WHEN voto = 'yes' THEN 1 ELSE -1 END) AS Total FROM tabela GROUP BY Nome order by Total desc
select will then be sorted correctly by the balance. Only it will not be null, but zeroed when both are equal.– Reginaldo Rigo