0
I have the following query
SELECT
(SUM(DISTINCT result)/COUNT(*))as total,
uf FROM users WHERE result > 0 and result IS NOT NULL
GROUP BY uf ORDER BY total DESC LIMIT 5
I want to select all 5 results without repeating the total values I’m using the most distinct yet it’s returning repeated total
0
Try to make use WITH QUERIES to process the data until you get the result you need.
Try using this SQL command and then tell us if it worked.
WITH
-- Lista os Totais por UF
query_totais AS
(
SELECT
SUM(result)/COUNT(*) AS total
, uf
FROM users
WHERE result > 0 and result IS NOT NULL
GROUP BY uf
),
-- Adiciona na query anterior a coluna "rownum",
-- que será como se fosse a classificação do registro.
-- Se houver o mesmo total em duas UFs, a primeira terá valor "1" e a segunda terá valor "2"
query_totais_com_rownum AS
(
SELECT
total
, uf
, ROW_NUMBER() OVER(PARTITION BY total ORDER BY total DESC, uf) AS rownum
FROM query_totais
)
-- Resultado Final
SELECT
total
, uf
FROM query_totais_com_rownum
WHERE rownum = 1
ORDER BY total DESC
LIMIT 5
Hello isn’t working either
Browser other questions tagged sql distinct
You are not signed in. Login or sign up in order to post.
Try to explain better what you want. If the total is equal, even if different UF, you want to list only one? Makes sense?
– anonimo
i have a table called user in it have the result(result each person has) in this same table I have the state that the person and I want to list the 5 greatest results of any table so that no repeat value for example sp and mg has total equal to 100 I will only take one of them that is sp and other not ....
– f3107
What database are you using?
– Daniel Giacomelli
am using mysql
– f3107
But you are using DISTINCT over the result field and not over the total. Also in the sum you only consider different values but in the quantity (COUNT) you consider all values.
– anonimo
OK understood then I have to put in total ,but how would it look
– f3107