Calculation of string occurrence

Asked

Viewed 59 times

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

inserir a descrição da imagem aqui

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

sql sql-server

  • As are these tables, the ids are unique and the names repeat, or id and names repeat?

  • I’ll put an image to clarify

  • I was wrong in the question, it is not to return the id, but the name

  • When you say 'balance' you mean the difference between Yes and No?

  • 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

  • 2

    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.

Show 1 more comment

1 answer

2


I believe that’s what you’re 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 didn’t notice your Edit, you only want the user with the highest balance as GOKU SSJ4 scored. Added TOP 1 to query.

  • 1

    (In the above case I need you to return Ann’s name, because you have a better score balance) ... TOP 1 if necessary returns only one.

  • perfect personal!

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.