I need to make a query in the database. mysql

Question

I have the following database.

CREATE TABLE IF NOT EXISTS futebol (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nome  VARCHAR(30) NOT NULL,
    estado VARCHAR(2) NOT NULL,
    brasileirao INT NOT NULL,
    copadobrasil INT NOT NULL,
    estadual INT NOT NULL
);

INSERT INTO futebol
(nome, estado, brasileirao, copadobrasil, estadual)
VALUES
("Flamento", "RJ", 5, 3, 33),
("Palmeiras", "SP", 9, 3, 23),
("Santos", "SP", 8, 1, 21),
("Coríntias", "SP", 7, 3, 27),
("São Paulo", "SP", 6, 0, 21),
("Vasco", "RJ", 4, 1, 23),
("Cruzeiro", "MG", 4, 5, 37),
("Internacional", "RS", 3, 1, 44),
("Grêmio", "RS", 2, 5, 36),
("Águia de Marabá", "PA", 0, 0, 0),
("Águia de Marabá", "PA", 0, 0, 1);

And now I need to do this consultation: The state that holds the most titles in the Brasileirão championship.

I managed to do this query below, but I suppose it is not quite correct.

SELECT MAX(estado) AS Estado, MAX(CampeonatoBrasileiro) AS Títulos
FROM (SELECT estado, SUM(brasileirao) AS CampeonatoBrasileiro
FROM futebol GROUP BY estado) AS Brasileirão;

Could you help me?

Answer 1

First determine the amount of titles per state and then order and take the largest.

SELECT estado, SUM(brasileirao) AS CampeonatoBrasileiro FROM futebol GROUP BY estado
ORDER BY 2 DESC LIMIT 1;