This is a multi-part message in MIME format.
--=_NextPart_000_001E_01C57BFE.5877E810
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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 =3D = atleta.dorsal_atl
WHERE (atleta.sexo_atl =3D 'F' AND = classificacao_principal.id_equipa=3D'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?
--=_NextPart_000_001E_01C57BFE.5877E810
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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_pontosFROM classificacao_principal
INNER JOIN atleta ON = classificacao_principal.dorsal =3D atleta.dorsal_atlWHERE (atleta.sexo_atl =3D 'F' AND classificacao_principal.id_equipa=3D'46')GROUP BY classificacao_principal.id_equipaORDER 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?
--=_NextPart_000_001E_01C57BFE.5877E810--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?|||That´s 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:
>> 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?
>
Showing posts with label 5877e810. Show all posts
Showing posts with label 5877e810. Show all posts
Wednesday, March 21, 2012
Subscribe to:
Posts (Atom)