---
title: "Hjälp med SQL sats..."
type: "forum-thread"
url: "https://www.webforum.nu/amne/asp/47179-hjälp-med-sql-sats"
topic: "ASP"
topic_url: "https://www.webforum.nu/amne/asp"
author: "sven"
published: "2002-07-04T10:55:33.000Z"
updated: "2002-07-05T09:20:59.000Z"
replies: 10
views: 266
page: 1
pages: 1
language: "sv-SE"
site: "webForum — webforum.nu"
rights: "Upphovsrätten till varje inlägg tillhör dess författare."
attribution: "Citera som: webForum, https://www.webforum.nu/amne/asp/47179-hjälp-med-sql-sats"
---

# Hjälp med SQL sats...

## #1 — sven, 2002-07-04T10:55Z

Sitter med lite statistik presentation men har kört fasr. Det är inga problem att presentera statistiken per fritidsgård och år. Men hur skall jag skriva om jag vill lägga ihop alla fritidsgårdarna och bara presentera per år?

Så här ser min kod ut:

```
<%
fritidsgard = Request.Form("u_gard")

if fritidsgard = "alla" then
SQL = "SELECT * FROM undersokning ORDER BY u_ar" 

[B]Här skall det hända[/B] 

else

SQL = "SELECT * FROM undersokning WHERE u_gard = '"& fritidsgard &"' ORDER BY u_ar"

End If
set rs = db.Execute(SQL)
Do while NOT rs.EOF

summa = rs("u_u12") + rs("u_13") + rs("u_14") + rs("u_15") + rs("u_16") + rs("u_17") + rs("u_18") + rs("u_19") + rs("u_20o")
a = rs("u_u12")/summa*100
b = rs("u_13")/summa*100
c = rs("u_14")/summa*100
d = rs("u_15")/summa*100
e = rs("u_16")/summa*100
f = rs("u_17")/summa*100
g = rs("u_18")/summa*100
h = rs("u_19")/summa*100
i = rs("u_20o")/summa*100

total = a + b + c + d + e + f + g + h + i

%> 

<table border="0" cellpadding="0" cellspacing="0" height="110" width="323" style="border-collapse: collapse" bordercolor="#111111">
  <tr>
  	<th width="66" height="10" nowrap align="left">&nbsp;<%=rs("u_ar")%></th>
  	<th width="220" height="10" nowrap align="left"><%=rs("u_gard")%></th>
  	<th width="45" height="10" nowrap align="left"></th>

  </tr>	
  <tr>
    <td width="66" height="10" nowrap>12 och under</td>
    <td width="220" height="10" nowrap><img src="red.gif" height="3" width="<%=FormatNumber(a,1)*2%>"></td>
    <td width="45" height="10" nowrap><%=FormatNumber(a,1)%> %</td>

  </tr>
  <tr>
    <td width="66" height="10" nowrap>13 år</td>
    <td width="220" height="10" nowrap><img src="lightblue.gif" height="3" width="<%=FormatNumber(b,1)*2%>"></td>
    <td width="45" height="10" nowrap><%=FormatNumber(b,1)%> %</td>
  </tr>
  <tr>
    <td width="66" height="10" nowrap>14 år</td>
    <td width="220" height="10" nowrap><img src="yellow.gif" height="3" width="<%=FormatNumber(c,1)*2%>"></td>
    <td width="45" height="10" nowrap><%=FormatNumber(c,1)%> %</td>
  </tr>
  <tr>
    <td width="66" height="10" nowrap>15 år</td>
    <td width="220" height="10" nowrap><img src="darkgreen.gif" height="3" width="<%=FormatNumber(d,1)*2%>"></td>
    <td width="45" height="10" nowrap><%=FormatNumber(d,1)%> %</td>
  </tr>
  <tr>
    <td width="66" height="10" nowrap>16 år</td>
    <td width="220" height="10" nowrap><img src="purple.gif" height="3" width="<%=FormatNumber(e,1)*2%>"></td>
    <td width="45" height="10" nowrap><%=FormatNumber(e,1)%> %</td>
  </tr>
  <tr>
    <td width="66" height="10" nowrap>17 år</td>
    <td width="220" height="10" nowrap><img src="orange.gif" height="3" width="<%=FormatNumber(f,1)*2%>"></td>
    <td width="45" height="10" nowrap><%=FormatNumber(f,1)%> %</td>
  </tr>
  <tr>
    <td width="66" height="10" nowrap>18 år</td>
    <td width="220" height="10" nowrap><img src="brown.gif" height="3" width="<%=FormatNumber(g,1)*2%>"></td>
    <td width="45" height="10" nowrap><%=FormatNumber(g,1)%> %</td>
  </tr>
  <tr>
    <td width="66" height="10" nowrap>19 år</td>
    <td width="220" height="10" nowrap><img src="green.gif" height="3" width="<%=FormatNumber(h,1)*2%>"></td>
    <td width="45" height="10" nowrap><%=FormatNumber(h,1)%> %</td>
  </tr>
  <tr>
    <td width="66" height="10" nowrap>20 och över</td>
    <td width="220" height="10" nowrap><img src="blue.gif" height="3" width="<%=FormatNumber(i,1)*2%>"></td>
    <td width="45" height="10" nowrap><%=FormatNumber(i,1)%> %</td>
  </tr>
  <tr>
    <td width="66" height="10" nowrap>Totalt</td>
    <td width="220" height="10" nowrap><img src="black.gif" height="3" width="<%=total*2%>"></td>
    <td width="45" height="10" nowrap><%=total%> %</td>
  </tr>
    <tr>
      <td width="66" height="10" nowrap>Anmärkning:</td>
      <td width="220" height="10" valign="top"><%=rs("u_anm")%></td>
      <td width="45" height="10" nowrap></td>
  </tr>
</table>
<br>
<%
rs.MoveNext
Loop
rs.CLose
%>
```

Någon som kan hjälpa mig...  för jag har... ingen aning hur jag skall göra...

Permalänk: https://www.webforum.nu/p/47179

## #2 — OveRRidE, 2002-07-04T11:11Z

```
if fritidsgard = "alla" then
   SQL = "SELECT * FROM undersokning ORDER BY u_ar"
else
   SQL = "SELECT * FROM undersokning WHERE u_gard = '"& fritidsgard &"' ORDER BY u_ar"
end If
```

 Nåt sånt kanske. :q Men du hade ju bara placerat ett **then** fel.

Permalänk: https://www.webforum.nu/p/713072

## #3 — sven, 2002-07-04T11:14Z

Ahhh... det där then... ok... det bara smög sig in :)

Men... om jag gör så skriver den ut alla fritidsgårdarna. Vad jag vill är att den skall lägga ihop fritidsgårdarnas statistik och presentera en total per år.

Permalänk: https://www.webforum.nu/p/713077

## #4 — LarsG, 2002-07-04T12:08Z

Du behöver använda group by och sum, exakt hur beror på hur dina tabeller ser ut.

Vad är u_12 u_13 osv ?

Permalänk: https://www.webforum.nu/p/713108

## #5 — sven, 2002-07-04T12:22Z

u_u12, u_13 osv är heltalstabeller...

Permalänk: https://www.webforum.nu/p/713112

## #6 — LarsG, 2002-07-04T12:58Z

Jaha, det var lite tunt.

```
q = "select u_ar,sum(u_12) , sum(u_13), sum(u_14),sum(u_15),sum(u_16),sum(u_17),sum(u_18),sum(u_19) ,sum(u_20o)
 from undesokning  group by u_ar  order by u_ar"
summa = rs(1) + rs(2) + rs(3) + rs(4) + rs(5) + rs(6) + rs(7) + rs(8) + rs(9)
a = rs(1)/summa*100
b = rs(2)/summa*100
c = rs(3)/summa*100
d = rs(4)/summa*100
e = rs(5)/summa*100
f = rs(6)/summa*100
g = rs(7)/summa*100
h = rs(8)/summa*100
i = rs(9)/summa*100
```

Det går att lägga in beräkningarna direkt i SQL-satsen också.

Permalänk: https://www.webforum.nu/p/713121

## #7 — sven, 2002-07-04T13:30Z

Vet inte riktigt om detta fungerar. Det verkar som om alla summorna blir lika.
Kolla här
<http://www.uk-fjallet.se/giraff/test.asp>  och titta på resultatet av Alla fritidsgårdar.
Förmodar att det inte loopar på riktigt sätt.
Här är hela koden nu:

```
<%
fritidsgard = Request.Form("u_gard")

if fritidsgard = "alla" then
SQL = " SELECT u_ar, sum(u_u12), sum(u_13), sum(u_14), sum(u_15), sum(u_16), sum(u_17), sum(u_18), sum(u_19), sum(u_20o) FROM undersokning GROUP BY u_ar ORDER BY u_ar "
set rs = db.Execute(SQL)

summa = rs(1) + rs(2) + rs(3) + rs(4) + rs(5) + rs(6) + rs(7) + rs(8) + rs(9)

a = rs(1)/summa*100
b = rs(2)/summa*100
c = rs(3)/summa*100
d = rs(4)/summa*100
e = rs(5)/summa*100
f = rs(6)/summa*100
g = rs(7)/summa*100
h = rs(8)/summa*100
i = rs(9)/summa*100

else

SQL = "SELECT * FROM undersokning WHERE u_gard = '"& fritidsgard &"' ORDER BY u_ar"
set rs = db.Execute(SQL)

summa = rs("u_u12") + rs("u_13") + rs("u_14") + rs("u_15") + rs("u_16") + rs("u_17") + rs("u_18") + rs("u_19") + rs("u_20o")

a = rs("u_u12")/summa*100
b = rs("u_13")/summa*100
c = rs("u_14")/summa*100
d = rs("u_15")/summa*100
e = rs("u_16")/summa*100
f = rs("u_17")/summa*100
g = rs("u_18")/summa*100
h = rs("u_19")/summa*100
i = rs("u_20o")/summa*100

End If

Do while NOT rs.EOF

total = a + b + c + d + e + f + g + h + i
%> 

<table border="0" cellpadding="0" cellspacing="0" height="110" width="323" style="border-collapse: collapse" bordercolor="#111111">
  <tr>
  	<th width="66" height="10" nowrap align="left">&nbsp;<%=rs("u_ar")%></th>
  	<% if fritidsgard <> "alla" then
  	%>
  	<th width="220" height="10" nowrap align="left"><%=rs("u_gard")%></th>
  	<%
  	else
  	%>
  	<th width="220" height="10" nowrap align="left"></th>
  	<%
  	end if
  	%>
  	<th width="45" height="10" nowrap align="left"></th>

  </tr>	
  <tr>
    <td width="66" height="10" nowrap>12 och under</td>
    <td width="220" height="10" nowrap><img src="red.gif" height="3" width="<%=FormatNumber(a,1)*2%>"></td>
    <td width="45" height="10" nowrap><%=FormatNumber(a,1)%> %</td>

  </tr>
  <tr>
    <td width="66" height="10" nowrap>13 år</td>
    <td width="220" height="10" nowrap><img src="lightblue.gif" height="3" width="<%=FormatNumber(b,1)*2%>"></td>
    <td width="45" height="10" nowrap><%=FormatNumber(b,1)%> %</td>
  </tr>
  <tr>
    <td width="66" height="10" nowrap>14 år</td>
    <td width="220" height="10" nowrap><img src="yellow.gif" height="3" width="<%=FormatNumber(c,1)*2%>"></td>
    <td width="45" height="10" nowrap><%=FormatNumber(c,1)%> %</td>
  </tr>
  <tr>
    <td width="66" height="10" nowrap>15 år</td>
    <td width="220" height="10" nowrap><img src="darkgreen.gif" height="3" width="<%=FormatNumber(d,1)*2%>"></td>
    <td width="45" height="10" nowrap><%=FormatNumber(d,1)%> %</td>
  </tr>
  <tr>
    <td width="66" height="10" nowrap>16 år</td>
    <td width="220" height="10" nowrap><img src="purple.gif" height="3" width="<%=FormatNumber(e,1)*2%>"></td>
    <td width="45" height="10" nowrap><%=FormatNumber(e,1)%> %</td>
  </tr>
  <tr>
    <td width="66" height="10" nowrap>17 år</td>
    <td width="220" height="10" nowrap><img src="orange.gif" height="3" width="<%=FormatNumber(f,1)*2%>"></td>
    <td width="45" height="10" nowrap><%=FormatNumber(f,1)%> %</td>
  </tr>
  <tr>
    <td width="66" height="10" nowrap>18 år</td>
    <td width="220" height="10" nowrap><img src="brown.gif" height="3" width="<%=FormatNumber(g,1)*2%>"></td>
    <td width="45" height="10" nowrap><%=FormatNumber(g,1)%> %</td>
  </tr>
  <tr>
    <td width="66" height="10" nowrap>19 år</td>
    <td width="220" height="10" nowrap><img src="green.gif" height="3" width="<%=FormatNumber(h,1)*2%>"></td>
    <td width="45" height="10" nowrap><%=FormatNumber(h,1)%> %</td>
  </tr>
  <tr>
    <td width="66" height="10" nowrap>20 och över</td>
    <td width="220" height="10" nowrap><img src="blue.gif" height="3" width="<%=FormatNumber(i,1)*2%>"></td>
    <td width="45" height="10" nowrap><%=FormatNumber(i,1)%> %</td>
  </tr>
  <tr>
    <td width="66" height="10" nowrap>Totalt</td>
    <td width="220" height="10" nowrap><img src="black.gif" height="3" width="<%=total*2%>"></td>
    <td width="45" height="10" nowrap><%=total%> %</td>
  </tr>
    <% 
    if fritidsgard <> "alla" then
  	%>
  <tr>
      <td width="66" height="10" nowrap>Anmärkning:</td>
      <td width="220" height="10" valign="top"><%=rs("u_anm")%></td>
      <td width="45" height="10" nowrap></td>
  </tr>
	<%
	end if
	%>  
</table>
<br>
<%
rs.MoveNext
Loop
rs.CLose
%>
```

Permalänk: https://www.webforum.nu/p/713133

## #8 — sven, 2002-07-04T13:37Z

Som jag tänkte...
Det är 

```
Do while NOT rs.EOF
```

 som sitter på fel ställe. Men var skall den sitta?

Permalänk: https://www.webforum.nu/p/713136

## #9 — doggelito, 2002-07-04T15:11Z

efter set rs...
så här:

```
set rs = db.Execute(SQL)

Do while NOT rs.EOF
```

på båda ställena!!! :)

Permalänk: https://www.webforum.nu/p/713179

## #10 — sven, 2002-07-05T08:16Z

Nepp... funkar inte... blir

Microsoft VBScript compilation error '800a03fb' 

Expected 'Loop' 

/giraff/undersokning/gardd.asp, line 30 

else
^

Alltså...if satsen gör att det blir något fel med loopen.

Permalänk: https://www.webforum.nu/p/713424

## #11 — sven, 2002-07-05T09:20Z

Nu fick jag det att funka.

Fick göra en table till och köra hela alltet ist. Finns säker ett bättre sätt men det funkade i alla fall.

Tack för hjälpen... alla

Permalänk: https://www.webforum.nu/p/713460

---

Tråden på webben: https://www.webforum.nu/amne/asp/47179-hjälp-med-sql-sats