|
Home > Archive > MS SQL Server > June 2005 > Query top x with sum
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Query top x with sum
|
|
| Jorge Ferreira 2005-06-28, 11:23 am |
| Hello everyone,
I'm making a query that is supposed to sum the 3 best results.
The query is this:
SELECT top 3 classificacao_princi
pal.id_equipa AS equipa, SUM(classificacao_pr
incipal.posicao) AS soma_pontos
FROM classificacao_princi
pal
INNER JOIN atleta ON classificacao_princi
pal.dorsal = atleta.dorsal_atl
WHERE (atleta.sexo_atl = 'F' AND classificacao_princi
pal.id_equipa='46')
GROUP BY classificacao_princi
pal.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?
| |
| Alejandro Mesa 2005-06-28, 11:23 am |
| Try,
select
id_equipa,
sum(posicao) as soma_pontos
from
(
SELECT top 3
classificacao_princi
pal.id_equipa AS equipa,
classificacao_princi
pal.posicao
FROM
classificacao_princi
pal
INNER JOIN
atleta
ON classificacao_princi
pal.dorsal = atleta.dorsal_atl
WHERE
atleta.sexo_atl = 'F'
AND classificacao_princi
pal.id_equipa='46'
order by
classificacao_princi
pal.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_princi
pal.id_equipa AS equipa, SUM(classificacao_pr
incipal.posicao) AS soma_pontos
> FROM classificacao_princi
pal
> INNER JOIN atleta ON classificacao_princi
pal.dorsal = atleta.dorsal_atl
> WHERE (atleta.sexo_atl = 'F' AND classificacao_princi
pal.id_equipa='46')
> GROUP BY classificacao_princi
pal.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_pr
incipal.posicao) not to
classificacao_princi
pal.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_princi
pal.id_equipa AS equipa, SUM(classificacao_pr
incipal.posicao) AS soma_pontos
> FROM classificacao_princi
pal
> INNER JOIN atleta ON classificacao_princi
pal.dorsal = atleta.dorsal_atl
> WHERE (atleta.sexo_atl = 'F' AND classificacao_princi
pal.id_equipa='46')
> GROUP BY classificacao_princi
pal.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?
| |
| Jorge Ferreira 2005-06-29, 3:23 am |
| Thatīs it.
Thank you for your answer.
Jorge
"Alejandro Mesa" < AlejandroMesa@discus
sions.microsoft.com> escreveu na
mensagem news:3D7AF53E-0A69-47AE-B66A- 62E07C4B3EE5@microso
ft.com...
> Try,
>
> select
> id_equipa,
> sum(posicao) as soma_pontos
> from
> (
> SELECT top 3
> classificacao_princi
pal.id_equipa AS equipa,
> classificacao_princi
pal.posicao
> FROM
> classificacao_princi
pal
> INNER JOIN
> atleta
> ON classificacao_princi
pal.dorsal = atleta.dorsal_atl
> WHERE
> atleta.sexo_atl = 'F'
> AND classificacao_princi
pal.id_equipa='46'
> order by
> classificacao_princi
pal.posicao asc
> ) as t1
> group by
> id_equipa
> go
>
> This solution does not take care about ties.
>
>
> AMB
>
> "Jorge Ferreira" wrote:
>
>
|
|
|
|
|