How to make a ranking with SQL?

Asked

Viewed 632 times

1

Well, I have a "voting uniform" table that receives in the columns "voto_uniforme1", "voto_uniforme2" and "voto_uniforme3" a value of type "BOOLEAN", where 1 means that the person voted in this uniform and 0 the other way, follows the logical table image: inserir a descrição da imagem aqui

With this command:

SELECT(SELECT COUNT(voto_uniforme1) FROM votos_uniforme WHERE votos_uniforme.voto_uniforme1 = 1) AS Uniforme1,(SELECT COUNT(voto_uniforme2) FROM votos_uniforme WHERE votos_uniforme.voto_uniforme2 = 1) AS Uniforme2, (SELECT COUNT(voto_uniforme3) FROM votos_uniforme WHERE votos_uniforme.voto_uniforme3 = 1) AS Uniforme3

He returns it to me:

inserir a descrição da imagem aqui

But I need him to show me how a ranking, for example who has the most votes comes first, who has the most amount between the other two comes second, and the smallest comes last third, what would be the best way to do that ?

mysql

1 answer

1


Make a label for you to have the uniform name and its quantity and join the 3 selections with UNION ALL, then create a select for ordering the quantity, example:

SELECT * FROM (
SELECT COUNT(voto_uniforme1) as quantidade, 'uniforme1' as uniforme 
        FROM votos_uniforme WHERE votos_uniforme.voto_uniforme1 = 1
UNION ALL
SELECT COUNT(voto_uniforme2) as quantidade, 'uniforme2' as uniforme 
        FROM votos_uniforme WHERE votos_uniforme.voto_uniforme2 = 1
UNION ALL
SELECT COUNT(voto_uniforme3) as quantidade, 'uniforme3' as uniforme 
        FROM votos_uniforme WHERE votos_uniforme.voto_uniforme3 = 1)
AS t ORDER BY quantidade desc

in this case will generate 3 lines with the order by quantity, different from the one that generated only one line.

To create the View it has to be without the SELECT external (does not accept View creation with Sub Query), creation example:

CREATE VIEW `db`.`View1` AS

    SELECT COUNT(voto_uniforme1) as quantidade, 'uniforme1' as uniforme 
            FROM votos_uniforme WHERE votos_uniforme.voto_uniforme1 = 1
    UNION ALL
    SELECT COUNT(voto_uniforme2) as quantidade, 'uniforme2' as uniforme 
            FROM votos_uniforme WHERE votos_uniforme.voto_uniforme2 = 1
    UNION ALL
    SELECT COUNT(voto_uniforme3) as quantidade, 'uniforme3' as uniforme 
            FROM votos_uniforme WHERE votos_uniforme.voto_uniforme3 = 1

and at the time of its use in the SELECT of your call View put the ordination, example:

SELECT * FROM View1 ORDER BY quantidade DESC
  • 1

    In this case this "DESC" orders from the largest to the smallest ?

  • 1

    @Lonetonberry this from the largest to the smallest, if you want in normal order remove the DESC or put ASC

  • 1

    An error appeared to me, is that I need to make a VIEW with this code and when I try to save as a VIEW shows this error: "#1349 - View’s SELECT contains a subquery in the FROM clause"

  • 1

    @Lonetonberry good ai you put another point, for creation of the view I did the explaining Edit, note in the answer how it should be created and how it should be used with ordering, is an example.

  • 1

    So in this case, sorting cannot be part of the view in any way ?

  • 1

    @Not Lonetonberry, because that would deprive you of using the View for other purposes such as filters, ordering and so on (this is normal in benches). If you want to use a Procedure (Procedure) you could!

Show 1 more comment

Browser other questions tagged mysql

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