Sortera data

[Gerteniua]

Hej jag blir tokig på detta:

Har följande SQL Fråga: som jag försöker sortera på "Snitt" men jag lyckas inte...

 

select s.namn, " & _

" (select avg(cdbl(resultat)/cdbl(serier)) from resultat where spelare = s.id ) as snitt ," & _

" (select sum(serier) from resultat where spelare = s.id ) as serier," & _

" (select avg(cdbl(resultat)/cdbl(serier)) from resultat where spelare = s.id and H_B = 'H') as snitt_hemma," & _

" (select sum(serier) from resultat where spelare = s.id and H_B = 'H') as serier_hemma, " & _

" (select avg(cdbl(resultat)/cdbl(serier)) from resultat where spelare = s.id and H_B = 'B') as snitt_borta," & _

" (select sum(serier) from resultat where spelare = s.id and H_B = 'B') as serier_borta, " & _

" (select cdbl(sum(poang))/cdbl(sum(serier)) from resultat where spelare = s.id) as poang_snitt, " & _

" (select max(resultat) from resultat where spelare = s.id ) as basta, " & _

" (select min(resultat) from resultat where spelare = s.id ) as samsta from medlemmar as s order by 2 DESC "

 

Tack på förhand.

Gert

[query]

Hej!

 

Några frågor om ditt problem:

 

Vad använder du för databas?

Vad menar du med "lyckas inte"? Sorterar den på fel grej? Får du något felmeddelande? I så fall, vad?

 

mvh,

query

 

[Gerteniua]

Jag använder access.

 

Det enda jag kan skriva är Order By 4 Desc el Asc då sorterar den på Snitt_Hemma. hittar ingen annan lösning. Har även provat med Order By 'snitt' DESC utan resultat..

 

[Magnus Ahlkvist]

Får du felmeddelande eller låter den bara bli att sortera? Om du får felmeddelande, vilket? Osv.

Lite mer information tycker jag att man kan kräva för att hjälpa till.

 

--

En röst talade till mig och sade:

”Le och var glad, ty det kunde vara värre”.

Så jag log, och jag var glad.

Och det blev värre.

 

[Gerteniua]

Den låter bli att sortera!

Här är koden:

sql = _

"select s.namn, " & _

" (select avg(cdbl(resultat)/cdbl(serier)) from resultat where spelare = s.id ) as snitt ," & _

" (select sum(serier) from resultat where spelare = s.id ) as serier," & _

" (select avg(cdbl(resultat)/cdbl(serier)) from resultat where spelare = s.id and H_B = 'H') as snitt_hemma," & _

" (select sum(serier) from resultat where spelare = s.id and H_B = 'H') as serier_hemma, " & _

" (select avg(cdbl(resultat)/cdbl(serier)) from resultat where spelare = s.id and H_B = 'B') as snitt_borta," & _

" (select sum(serier) from resultat where spelare = s.id and H_B = 'B') as serier_borta, " & _

" (select cdbl(sum(poang))/cdbl(sum(serier)) from resultat where spelare = s.id) as poang_snitt, " & _

" (select max(resultat) from resultat where spelare = s.id ) as basta, " & _

" (select min(resultat) from resultat where spelare = s.id ) as samsta from medlemmar as s ORDER BY 2 DESC"

 

set rs = connect.execute(sql)

if rs.eof then

response.write "Inga resultat funna <br>"

else

Response.write "<table>"

Response.write "<tr><td>Namn</td> <td>Snitt</td> <td>Ser</td> " & _

"<td>Hemma</td><td>Ser</td><td>Borta</td>" & _

"<td>Ser</td><td>Poäng</td><td>Bästa</td><td>Sämsta</td></tr>"

while not rs.eof

 

Response.write "<td align=center nowrap>" & "<a href='Spelare.asp?Spelare=" &_

rs("Namn") & "'>"& rs("Namn") &"</a></td> "

 

if isnull(rs("Snitt_hemma")) then

 

Response.write "<td align=right>" & round(0+rs("Snitt"),1) & "</td> "

Response.write "<td align=right>" & rs("Serier") & "</td> "

Response.write "<td align=right> 0 </td> <td align=right> 0 </td> "

Response.write "<td align=right>" & round(0+rs("Snitt_borta"),1) & "</td> "

Response.write "<td align=right>" & rs("Serier_borta") & "</td> "

Response.write "<td align=right>" & round(0+rs("Poang_snitt"),2) & "</td> "

Response.write "<td align=right>" & rs("Basta") & "</td> "

Response.write "<td align=right>" & rs("Samsta") & "</td> "

 

elseif isnull(rs("Snitt_borta")) then

 

Response.write "<td align=right>" & round(0+rs("Snitt"),1) & "</td> "

Response.write "<td align=right>" & rs("Serier") & "</td> "

Response.write "<td align=right>" & round(0+rs("Snitt_hemma"),1) & "</td> "

Response.write "<td align=right>" & rs("Serier_hemma") & "</td> "

Response.write "<td align=right> 0 </td> <td align=right> 0 </td> "

Response.write "<td align=right>" & round(0+rs("Poang_snitt"),2) & "</td> "

Response.write "<td align=right>" & rs("Basta") & "</td> "

Response.write "<td align=right>" & rs("Samsta") & "</td> "

 

else

 

Response.write "<td align=right>" & round(0+rs("Snitt"),1) & "</td> "

Response.write "<td align=right>" & rs("Serier") & "</td> "

Response.write "<td align=right>" & round(0+rs("Snitt_hemma"),1) & "</td> "

Response.write "<td align=right>" & rs("Serier_hemma") & "</td> "

Response.write "<td align=right>" & round(0+rs("Snitt_borta"),1) & "</td> "

Response.write "<td align=right>" & rs("Serier_borta") & "</td> "

Response.write "<td align=right>" & round(0+rs("Poang_snitt"),2) & "</td> "

Response.write "<td align=right>" & rs("Basta") & "</td> "

Response.write "<td align=right>" & rs("Samsta") & "</td> "

 

end if

 

Response.write "</tr>"

rs.movenext

wend

 

 

[Gerteniua]

Så här ska det vara:

sql = _

" select s.namn, " & _

" avg(cdbl(resultat)/cdbl(serier)) as snitt ," & _

" sum(serier) as totalt_serier," & _

" (select avg(cdbl(resultat)/cdbl(serier)) from resultat where spelare = s.id and H_B = 'H') as snitt_hemma," & _

" (select avg(cdbl(resultat)/cdbl(serier)) from resultat where spelare = s.id and H_B = 'B') as snitt_borta," & _

" sum(iif(H_B='H',serier,0)) as serier_hemma, " & _

" sum(iif(H_B='B',serier,0)) as serier_borta, " & _

" cdbl(sum(poang))/cdbl(sum(serier)) as poang_snitt, " & _

" max(resultat) as basta, " & _

" min(resultat) as samsta from medlemmar as s left outer join resultat as r on r.spelare = s.id" & _

" group by s.namn, id ORDER BY 2 DESC"