Query top x with sum

Hello everyone,
I'm making a query that is supposed to sum the 3 best results.
The query is this:
SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificacao_principal.posicao) AS soma_pontos
FROM classificacao_principal
INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
GROUP BY classificacao_principal.id_equipa
ORDER BY soma_pontos ASC
This query resturns me the top 3 results, but the SUM is made with all the results available. What i want is to sum only the 3 best results.
Any ideas?
Try,
select
id_equipa,
sum(posicao) as soma_pontos
from
(
SELECT top 3
classificacao_principal.id_equipa AS equipa,
classificacao_principal.posicao
FROM
classificacao_principal
INNER JOIN
atleta
ON classificacao_principal.dorsal = atleta.dorsal_atl
WHERE
atleta.sexo_atl = 'F'
AND classificacao_principal.id_equipa='46'
order by
classificacao_principal.posicao asc
) as t1
group by
id_equipa
go
This solution does not take care about ties.
AMB
"Jorge Ferreira" wrote:

> Hello everyone,
> I'm making a query that is supposed to sum the 3 best results.
> The query is this:
> SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificacao_principal.posicao) AS soma_pontos
> FROM classificacao_principal
> INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
> GROUP BY classificacao_principal.id_equipa
> ORDER BY soma_pontos ASC
> This query resturns me the top 3 results, but the SUM is made with all the results available. What i want is to sum only the 3 best results.
> Any ideas?
|||Your ORDER BY soma_pontos applying the TOP cluase to the
SUM(classificacao_principal.posicao) not to
classificacao_principal.id_equipa
Try removing the Order by and see if it works.
Thanks
Ravi
"Jorge Ferreira" wrote:

> Hello everyone,
> I'm making a query that is supposed to sum the 3 best results.
> The query is this:
> SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificacao_principal.posicao) AS soma_pontos
> FROM classificacao_principal
> INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
> GROUP BY classificacao_principal.id_equipa
> ORDER BY soma_pontos ASC
> This query resturns me the top 3 results, but the SUM is made with all the results available. What i want is to sum only the 3 best results.
> Any ideas?
|||Thats it.
Thank you for your answer.
Jorge
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> escreveu na
mensagem news:3D7AF53E-0A69-47AE-B66A-62E07C4B3EE5@.microsoft.com...
> Try,
> select
> id_equipa,
> sum(posicao) as soma_pontos
> from
> (
> SELECT top 3
> classificacao_principal.id_equipa AS equipa,
> classificacao_principal.posicao
> FROM
> classificacao_principal
> INNER JOIN
> atleta
> ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE
> atleta.sexo_atl = 'F'
> AND classificacao_principal.id_equipa='46'
> order by
> classificacao_principal.posicao asc
> ) as t1
> group by
> id_equipa
> go
> This solution does not take care about ties.
>
> AMB
> "Jorge Ferreira" wrote:
>