---
title: "Sviter..."
type: "forum-thread"
url: "https://www.webforum.nu/amne/databaser-sql/155986-sviter"
topic: "Databaser & SQL"
topic_url: "https://www.webforum.nu/amne/databaser-sql"
author: "asp"
published: "2007-01-15T12:37:42.000Z"
updated: "2007-01-15T18:58:22.000Z"
replies: 6
views: 420
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/databaser-sql/155986-sviter"
---

# Sviter...

## #1 — asp, 2007-01-15T12:37Z

Hoho...

För att räkna antalet vinster använder jag följande kod:

```
SQL = "SELECT Count(*) FROM Matcher WHERE " & _
"(homeID = " & ID & " AND homeGoals > awayGoals) OR " & _
"(awayID = " & ID & " AND homeGoals < awayGoals)"
```

Nu vill jag räkna ut flest matcher i rad utan förlust. Jag vet hur jag löser detta i ASP kod, men tänkte om det gick att ordna direkt i SQL satsen.

/ Marcus

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

## #2 — Peter S, 2007-01-15T14:03Z

Med referens till \<<http://www.sqlteam.com/item.asp?ItemID=12654>\> kan vi skriva

```
select case when
         (homeid = <ID> and homegoals >= awaygoals)
           or
         (awayid = <ID> and awaygoals >= homegoals) then 1 else 0
       end as point
     , (select count(*)
          from matcher
         where <date column> < m.<date column>
           and ((homeid = <ID> and homegoals < awaygoals)
                 or
               (awayid = <ID> and awaygoals < homegoals))) as rg
     , count(*) as streak
  from matcher m
 where (homeid = <ID> and homegoals >= awaygoals)
    or (awayid = <ID> and awaygoals >= homegoals)
group
    by point
     , rg
order
    by streak desc
 limit 1
```

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

## #3 — asp, 2007-01-15T14:50Z

Det var väl ganska väntat att jag skulle få ett felmeddelande...

```
SQL = "select case when " & _
"(homeid = " & ID & " and homegoals >= awaygoals) " & _
"or " & _
"(awayid = " & ID & " and awaygoals >= homegoals) then 1 else 0 " & _
"end as point " & _
", (select count(*) " & _
"from matcher " & _
"where matchdate < m.matchdate " & _
"and ((homeid = " & ID & " and homegoals < awaygoals) " & _
"or " & _
"(awayid = 1 and awaygoals < homegoals))) as rg " & _
", count(*) as streak " & _
"from matcher m " & _
"where (homeid = " & ID & " and homegoals >= awaygoals) " & _
"or (awayid = " & ID & " and awaygoals >= homegoals) " & _
"group " & _
"by point " & _
", rg " & _
"order " & _
"by streak desc " & _
"limit 1"
```

...ger...

```
Error Type:
(0x80004005)
Unspecified error 
/stat/test.asp, line 38
```

Line 38 är koden då jag kör SQL-satsen. Den klagar alltså på SQL-satsen... ;-)

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

## #4 — Peter S, 2007-01-15T15:31Z

Vilket DBMS? Om SQL Server:

```
select top 1 case when
         (homeid = <ID> and homegoals >= awaygoals)
           or
         (awayid = <ID> and awaygoals >= homegoals) then 1 else 0
       end as point
     , (select count(*)
          from matcher
         where matchdate < m.matchdate
           and ((homeid = <ID> and homegoals < awaygoals)
                 or
               (awayid = <ID> and awaygoals < homegoals))) as rg
     , count(*) as streak
  from matcher m
 where (homeid = <ID> and homegoals >= awaygoals)
    or (awayid = <ID> and awaygoals >= homegoals)
group
    by point
     , rg
order
    by streak desc
```

Läs gärna @nders [guide](http://www.webforum.nu/showthread.php?t=135903) till vad som bör inkluderas då frågor ställs.

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

## #5 — asp, 2007-01-15T17:21Z

Access... sorry... glömmer alltid av sånt =)

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

## #6 — Peter S, 2007-01-15T17:24Z

Testa

```
select top 1
       iif((homeid = <ID> and homegoals >= awaygoals)
             or
           (awayid = <ID> and awaygoals >= homegoals),1,0) as point
     , (select count(*)
          from matcher
         where matchdate < m.matchdate
           and ((homeid = <ID> and homegoals < awaygoals)
                  or
                (awayid = <ID> and awaygoals < homegoals))) as rg
     , count(*) as streak
  from matcher m
 where (homeid = <ID> and homegoals >= awaygoals)
    or (awayid = <ID> and awaygoals >= homegoals)
group
    by point
     , rg
order
    by streak desc
```

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

## #7 — asp, 2007-01-15T18:58Z

\<ID\> Skall väl ersättas med " & ID & "  ??

```
SQL = "select top 1 " & _
"       iif((homeid = " & ID & " and homegoals >= awaygoals) " & _
"             or " & _
"           (awayid = " & ID & " and awaygoals >= homegoals),1,0) as point " & _
"     , (select count(*) " & _
"          from matcher " & _
"         where matchdate < m.matchdate " & _
"           and ((homeid = " & ID & " and homegoals < awaygoals) " & _
"                  or " & _
"                (awayid = " & ID & " and awaygoals < homegoals))) as rg " & _
"     , count(*) as streak " & _
"  from matcher m " & _
" where (homeid = " & ID & " and homegoals >= awaygoals) " & _
"    or (awayid = " & ID & " and awaygoals >= homegoals) " & _
"group " & _
"    by point " & _
"     , rg " & _
"order " & _
"    by streak desc"
```

Gav...

```
Error Type:
Microsoft JET Database Engine (0x80040E21)
You tried to execute a query that does not include the specified expression 'IIf(homeid=334 And homegoals>=awaygoals Or awayid=334 And awaygoals>=homegoals,1,0)' as part of an aggregate function.
```

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

---

Tråden på webben: https://www.webforum.nu/amne/databaser-sql/155986-sviter