Snittlista till bowlingklubb

[Poj]

Hej. Jag försöker göra en snittlista till en klubb men får inte till det som jag vill.

Jag har skapat en tabell med id, namn, datum, hemma, serie1, serie2, serie3 och serie4.

serie1 till 4 har NULL som standardvärde. hemma är en kryssruta (bit).

Här är frågan som den ser ut nu:

SELECT namn, datum, hemma,sum(serie1)+sum(serie2)+sum(serie3)+sum(serie4),count(serie1)+count(serie2)+ count(serie3)+count(serie4),(sum(serie1)+ sum(serie2)+ sum(serie3)+ sum(serie4))/(count(serie1)+count(serie1)+ count(serie3)+count(serie4))
FROM bowlingsnitt
WHERE datum >= 2013-01-01
GROUP BY namn
ORDER BY (sum(serie1)+sum(serie2)+sum(serie3)+sum(serie4))/(count(serie1)+count(serie2)+ count(serie3)+count(serie4)) desc

 

Här får jag fram namn, datum, hemma eller bortamatch, snitt ,antal serier och totalslagning.

Nu till frågorna:

Jag skulle vilja få fram snittet på hemmamatcher och snittet på bortamatcher också fast listan skall sorteras på totalsnittet, hur gör jag det?

Hur gör jag för att få fram allt från dagens datum och säg 12 månader tillbaks? nu har jag skrivit in ett fast datum men jag vill ha det automatiskt.

När jag har en spelare som spelat bara 3 serier så visas bara antal serier och inte totalslagning eller snitt, varför?

 

MVH Per-Olof

[jarlh]

Först lite allmänna synpunkter. Varför heter tabellen bowlingsnitt när den innehåller resultat för bowlingserier? (Du ska ju räkna ut snittet med hjälp av resultaten.)

 

Sedan gör du select på datum, men vilket datum är det du vill ha? (En spelare lär ju ha spelat matcher på olika datum...) Generella regeln för GROUP BY är:

Om en kolumn refereras i select-listan så ska den antingen listas i group by eller vara som argument till en set-funktion!
 

Att summera och räkna snitt på flera kolumner i samma tabell blir krångligt om det förekommer NULL. Du måste då ha koll på om spelarna har spelat någon match alls hemma/borta etc, och om det finns endast NULL etc. (Annars riskerar du åka på division med 0, eller addition av NULL.)

 

Om en spelare inte spelar alla 4 serier i en match ska då bara de spelade räknas? (Man får alltså inte 0 för överhoppad serie?)

 

Själv skulle jag funderat på en annan datamodell:

 

create table matcher (
  matchid int primary key,
  spelare varchar(50) not null,
  datum date not null,
  hemma boolean not null);

create table matchserier (
  matchid int,
  serieno int,
  resultat decimal(5,2) not null,
  primary key(matchid,serieno),
  check (serieno in (1,2,3,4)),
  check (resultat between 0 and 10),
  foreign key (matchid) references matcher);

 

select spelare,
       avg(resultat) as snitt,
       avg(case when hemma then resultat end) as hemmasnitt,
       avg(case when not hemma then resultat end) as bortasnitt
from matcher left join matchserier
  on matcher.matchid = matchserier.matchid

where datum >= current_date - interval '1' year

group by spelare
order by snitt;

 

Notera att hur man räknar ut dagens datum minus ett år är enligt standard, men många databaser följer inte standarden - så du kan behöva göra på annat sätt!

[Poj]

Först lite allmänna synpunkter. Varför heter tabellen bowlingsnitt när den innehåller resultat för bowlingserier? (Du ska ju räkna ut snittet med hjälp av resultaten.)

 

Sedan gör du select på datum, men vilket datum är det du vill ha? (En spelare lär ju ha spelat matcher på olika datum...) Generella regeln för GROUP BY är:

Om en kolumn refereras i select-listan så ska den antingen listas i group by eller vara som argument till en set-funktion!

 

Att summera och räkna snitt på flera kolumner i samma tabell blir krångligt om det förekommer NULL. Du måste då ha koll på om spelarna har spelat någon match alls hemma/borta etc, och om det finns endast NULL etc. (Annars riskerar du åka på division med 0, eller addition av NULL.)

 

Om en spelare inte spelar alla 4 serier i en match ska då bara de spelade räknas? (Man får alltså inte 0 för överhoppad serie?)

 

Själv skulle jag funderat på en annan datamodell:

 

create table matcher (

  matchid int primary key,

  spelare varchar(50) not null,

  datum date not null,

  hemma boolean not null);

 

create table matchserier (

  matchid int,

  serieno int,

  resultat decimal(5,2) not null,

  primary key(matchid,serieno),

  check (serieno in (1,2,3,4)),

  check (resultat between 0 and 10),

  foreign key (matchid) references matcher);

 

select spelare,

       avg(resultat) as snitt,

       avg(case when hemma then resultat end) as hemmasnitt,

       avg(case when not hemma then resultat end) as bortasnitt

from matcher left join matchserier

  on matcher.matchid = matchserier.matchid

where datum >= current_date - interval '1' year

group by spelare

order by snitt;

 

Notera att hur man räknar ut dagens datum minus ett år är enligt standard, men många databaser följer inte standarden - så du kan behöva göra på annat sätt!

Hej tack för svaret, jag skall testa detta i helgen.

Jag döpte min tabell till bowlingsnitt för att lätt hålla reda på den, jag testar i min wamp server på hem datorn. 

En spelare kan spela allt mellan 1 till 4 serier i en match, han kan bli utbytt om han spelar dåligt.

Alla spelade serier skall räknas med så spelar han 2 serier skall båda räknas med.

 

MVH Per-Olof

[Poj]

Först lite allmänna synpunkter. Varför heter tabellen bowlingsnitt när den innehåller resultat för bowlingserier? (Du ska ju räkna ut snittet med hjälp av resultaten.)

 

Sedan gör du select på datum, men vilket datum är det du vill ha? (En spelare lär ju ha spelat matcher på olika datum...) Generella regeln för GROUP BY är:

Om en kolumn refereras i select-listan så ska den antingen listas i group by eller vara som argument till en set-funktion!

 

Att summera och räkna snitt på flera kolumner i samma tabell blir krångligt om det förekommer NULL. Du måste då ha koll på om spelarna har spelat någon match alls hemma/borta etc, och om det finns endast NULL etc. (Annars riskerar du åka på division med 0, eller addition av NULL.)

 

Om en spelare inte spelar alla 4 serier i en match ska då bara de spelade räknas? (Man får alltså inte 0 för överhoppad serie?)

 

Själv skulle jag funderat på en annan datamodell:

 

create table matcher (

  matchid int primary key,

  spelare varchar(50) not null,

  datum date not null,

  hemma boolean not null);

 

create table matchserier (

  matchid int,

  serieno int,

  resultat decimal(5,2) not null,

  primary key(matchid,serieno),

  check (serieno in (1,2,3,4)),

  check (resultat between 0 and 10),

  foreign key (matchid) references matcher);

 

select spelare,

       avg(resultat) as snitt,

       avg(case when hemma then resultat end) as hemmasnitt,

       avg(case when not hemma then resultat end) as bortasnitt

from matcher left join matchserier

  on matcher.matchid = matchserier.matchid

where datum >= current_date - interval '1' year

group by spelare

order by snitt;

 

Notera att hur man räknar ut dagens datum minus ett år är enligt standard, men många databaser följer inte standarden - så du kan behöva göra på annat sätt!

Jag kan inte skapa tabellen matchserier, jag får felmeddelandet  #1215 - Cannot add foreign key constraint .

Vad betyder det och hur kommer jag vidare?

Databasen är InnoDB.

[Poj]

Jag kan inte skapa tabellen matchserier, jag får felmeddelandet  #1215 - Cannot add foreign key constraint .

Vad betyder det och hur kommer jag vidare?

Databasen är InnoDB.

Jag har löst detta.

 

Nästa fråga

Hur fungerar denna databas? det är mycket nytt som jag inte känner till i sql koden.

Hur ska jag mata in resultatet?

[Poj]

Databas modellen fungerar inte.

Jag har antagit att "serieno" skall innehålla antal spelade serier och "resultat" totala poängen på alla spelade serier i den matchen. 

men den räknar inte ut snittet och jag lyckades bara få in en spelare i tabellerna.

Jag förstår inte syftet med att ha 2 tabeller då varje inmatning resulterar i en ny rad i båda tabellerna.

 

Jag har gjort om det lite och slagit ihop tabellerna:

create table matchersnitt (
matchid int,
spelare varchar(50) not null,
datum date not null,
hemma boolean not null,
serieno int,
resultat decimal(5,2) not null,
primary key(matchid,serieno),
check (serieno in (1,2,3,4)),
check (resultat between 0 and 10));

 

och frågan:

SELECT spelare, avg(resultat/serieno) as snitt,
avg(case when hemma then resultat/serieno end) as hemmasnitt,
avg(case when not hemma then resultat/serieno end) as bortasnitt
FROM matchersnitt
WHERE datum >= current_date - interval '1' year
GROUP BY spelare
ORDER BY snitt desc

 

Detta ger önskat resultat med ett undantag, jag får en massa decimaler !! efter kommatecknet, i tabellen är det bara 2 men troligen blir det fler av beräkningen.

Hur löser jag det ?

jag vill bara ha 2 decimaler.