Just nu i M3-nätverket
Jump to content

Sortera data


Gerteniua

Recommended Posts

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

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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..

 

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

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"

 

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.



×
×
  • Create New...