How to do sum of columns without nested loops?

Hello, I think I saw an aspfaq about this but I cannot find it. How do
I fix this so I get rid of the nested sql statements?

set conn = server.CreateObject ("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\brink-premfs1\sites\[deleted];" & _
"Persist Security Info=False"
sql = "SELECT id, username, rating FROM userlist ORDER BY username
asc"
set rs = conn.execute(sql)

do while not rs.eof
response.write ""
sql = "SELECT SUM(kills) as totalkills, SUM(losses) as totallosses
FROM skirmishresults where playerid = " & rs("id")
set rs2 = conn.execute(sql)

response.write "" & rs("username")

response.write "" & " "
if not isNull(rs2("totalkills")) then
response.write FormatNumber(rs2("totalkills"),1)
end if

response.write "" & " "
if not isNull(rs2("totallosses")) then
response.write FormatNumber(rs2("totallosses"),1)
end if

response.write "" & " "
if not isNull(rs2("totalkills")) then
response.write
FormatNumber(CDbl(rs2("totalkills"))/CDbl(rs2("totallosses") ),1)
end if

response.write "" & rs("rating")

rs.movenext
loop
set rs=nothing
set conn=nothing

Re: How to do sum of columns without nested loops?

SELECT
userlist.id,
userlist.username,
userlist.rating,
SUM(skirmishresults.kills) AS totalkills,
SUM(skirmishresults.losses) AS totallosses
FROM
userlist INNER JOIN skirmishresults ON
userlist.id=skirmishresults.playerID
GROUP BY userlist.id, userlist.username, userlist.rating
ORDER BY userlist.username

Or do a left join to include players who have no records in the
skirmishresults yet.

Ray at work

"mike" wrote in message
news:1131642038.181183.59620@f14g2000cwb.googlegroups.com...
> Hello, I think I saw an aspfaq about this but I cannot find it. How do
> I fix this so I get rid of the nested sql statements?
>
> set conn = server.CreateObject ("ADODB.Connection")
> conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=\\brink-premfs1\sites\[deleted];" & _
> "Persist Security Info=False"
> sql = "SELECT id, username, rating FROM userlist ORDER BY username
> asc"
> set rs = conn.execute(sql)
>
> do while not rs.eof
> response.write ""
> sql = "SELECT SUM(kills) as totalkills, SUM(losses) as totallosses
> FROM skirmishresults where playerid = " & rs("id")
> set rs2 = conn.execute(sql)
>
> response.write "" & rs("username")
>
> response.write "" & " "

Re: How to do sum of columns without nested loops?

works great ray, thanks!