Error while performing max(sum())

Asked

Viewed 47 times

0

I’m trying to solve the problem to find the state with the largest number of cities starting with saint/saint. For that I realized this view: 

CREATE VIEW SANT AS ( 
SELECT E.NOME ESTADOS, M.NOME CIDADES, COUNT('CIDADES') AS CNT FROM ESTADO E, MUNICIPIO M 
WHERE E.CODESTADO = M.CODESTADO
AND M.NOME LIKE 'Santa%' OR M.NOME LIKE 'Santo%' 
GROUP BY M.NOME, E.NOME
);

after that, I have to sum with Count and take the maximum value obtained. but when I do: 

select estados, sum(cnt) as soma from sant
where soma = (select max('soma') from sant)
group by estados

of the error because the sum.... and when I do 

select estados, sum(cnt) as soma from sant
where cnt= (select max(cnt) from sant)
group by estados

the value returned is 8 for all states.

I was able to solve the problem with another view and a select, but I know that if only a select aligned would be easier the database would have to spend a lower cost. The question is how could I do it?

follows below as I got the resolution. 

CREATE VIEW SOMA_SANT AS ( 
SELECT ESTADOS, SUM(CNT) SOMA FROM SANT 
GROUP BY ESTADOS
); 

SELECT ESTADOS  FROM SOMA_SANT 
WHERE SOMA = (SELECT MAX(SOMA) FROM SOMA_SANT)
mysql sql select-sql

  • Select * from Sant order by cnt desc , the view already totals.

  • Thus returns the number of cities with that name per state. for example: Indian saint in Tocantins has 4. but I want the state with the largest number of cities with these saint names. so I need to do the sum per state and then take the max.

  • Take M.NAME CITIES from view , try so.

  • Keep telling it the same way

  • CREATE VIEW SANT AS ( SELECT E.NAME STATES, COUNT('CITIES') AS CNT FROM STATE E, MUNICIPIO M WHERE E.CODESTADO = M.CODESTADO AND M.NAME LIKE 'Santa%' OR M.NAME LIKE 'Santo%' GROUP BY E.NAME );

1 answer

0


Good evening Vinicius all right? We can solve this in a simpler way but I also have an observation about the setup of your view: Be careful with the mixture of AND and OR. When using them together, choose to add parentheses where there is OR. See how your WHERE would look:

WHERE E.CODESTADO = M.CODESTADO AND (M.NOME LIKE 'Santa%' OR M.NOME LIKE '%Santo%')

About your question, we can resolve with a query:

SELECT COUNT(*) AS QTDE, A.NOME AS NOMEESTADO FROM
  ESTADO AS A
  JOIN MUNICIPIO AS B ON B.CODESTADO = A.CODESTADO
WHERE B.NOME LIKE '%SANTO%' OR B.NOME LIKE '%SANTA%'
GROUP BY A.NOME --AGRUPANDO POR NOME PARA REALIZAR A CONTAGEM CORRETAMENTE
ORDER BY COUNT(*) DESC --COMO TEMOS OS REGISTROS AGRUPADOS, PODEMOS ORDENAR POR AQUI
LIMIT 1 --CASO QUEIRA RETORNAR TODOS OS REGISTROS, REMOVA ESTE CÓDIGO

Code I used to test: http://sqlfiddle.com/#! 9/8f0803/2

Browser other questions tagged mysql sql select-sql

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