Calculation of string occurrence

Navigation
1

I have a table with 3 columns id , nome and voto . They are populated with many records, and I need a calculation that returns the name with the best "balance" of votes . The voto column can only receive values like 'yes' and 'no', being of string type.

What I need is to check the amount of 'yes' and 'no' for all records and returns those who have a larger balance. If you have a yes, add a dot, if not, remove a dot. I am not able to work with strings.

I'm using SQL Server

In the above case, I need to return the name of Ann because it has a better balance of points

NOTE: ids are unique and names can be repeated

    
asked by anonymous 22.08.2016 / 19:12

1 answer

2

I believe this is what you are looking for: 

   SELECT TOP 1 name, SUM(CASE WHEN vote = 'yes' THEN 1 ELSE -1 END) AS Total 
   FROM table1 
   GROUP BY Name 
   ORDER BY Total desc

EDIT2: I did not notice your edit, you only want the user with the highest balance as the GOKU SSJ4 scored. Added TOP 1 to query.

    
22.08.2016 / 20:53
Composite update Get json array value for string - Java