Ryhmittely ja koostefunktiot (GROUP BY ja HAVING)

GROUP BY <kenttä> [,] ...
HAVING <koostefunktio> <vertailuehto> <arvo tai alikysely>

SQL:stä löytyy seuraavat koostefunktiot:

AVGkeskiarvo

COUNTarvojen lukumäärä

MAXsuurin arvo

MINpienin arvo

SUMsumma

Koostefunktioita voidaan käyttää vain joko SELECT-lauseen kenttäluettelossa tai HAVING-lauseessa. WHERE-lauseessa niitä ei voi käyttää. COUNT(*) laskee kaikkien haun tulosrivien lukumäärän myös sellaisten, jotka ovat NULL. COUNT(kentännimi) laskee vain niiden rivien lukumäärän, jotka eivät ole NULL. Muut koostefunktiot jättävät NULL-arvot huomioimatta. Jos koostefunktion tuloskentälle ei määritellä mitään nimeä, niin nimen muodostuminen on hyvin ohjelmistokohtaista. Jos samassa haussa on sekä koostefunktioita että tavallisia kenttiä, on lopputulos ryhmiteltävä tavallisten kenttien perusteella. Ryhmittely tapahtuu GROUP BY –määreellä, jossa pitää luetella kaikki kyselyn lopputulokseen tulevat tavalliset kentät siinä järjestyksessä, jossa halutaan ryhmittelyn tapahtuvan.

Tulosjoukon käsittely tapahtuu siis seuraavasti:

Lasketaan opiskelijoiden lukumäärä:

SELECT COUNT(*) AS lukumäärä
FROM Opiskelija;

LUKUMÄÄRÄ
-----------
8

1 record(s) selected.

Lasketaan monellako opiskelijalla on sähköpostiosoite:

SELECT COUNT(sahkoposti) AS lukumäärä
FROM Opiskelija;

LUKUMääRä
-----------
6

1 record(s) selected.

Kaikkien niiden tenttisuoritusten keskiarvo joiden arvo on yli 2:

SELECT AVG(arvosana) AS Keskiarvo
FROM tenttii
WHERE arvosana> 2;

KESKIARVO
---------------------------------
2,703125000000000000000000000000

1 record(s) selected.

Jos kyselyssä on sekä tavallisia kenttiä, että koostefunktioita niin kaikki tavalliset kentät PITää luetella GROUP BY-lauseessa!

Kaikkien tenttisuoritusten keskiarvo ryhmiteltynä opiskelijan hetun mukaan:

SELECT opiskelija, AVG(arvosana) AS keskiarvo
FROM tenttii
GROUP BY opiskelija;
OPISKELIJA KESKIARVO
----------- ---------------------------------
010101-011P 1,400000000000000000000000000000
111111-111P 1,600000000000000000000000000000
111112-111P 2,000000000000000000000000000000
111113-111P 1,500000000000000000000000000000
111114-111P 2,142857142857142857142857142857
121212-112P 1,800000000000000000000000000000
230173-000L 2,000000000000000000000000000000

7 record(s) selected.

Kaikkien TIE160-kurssin tenttisuoritusten keskiarvo ryhmiteltynä opiskelijan mukaan:

SELECT opiskelija, AVG(arvosana) AS keskiarvo
FROM tenttii
WHERE kurssi = 'TIE160'
GROUP BY opiskelija;
OPISKELIJA KESKIARVO
----------- ---------------------------------
010101-011P 1,250000000000000000000000000000
111111-111P 2,000000000000000000000000000000
111112-111P 3,000000000000000000000000000000
111113-111P 2,750000000000000000000000000000
111114-111P 2,500000000000000000000000000000
121212-112P 1,750000000000000000000000000000
230173-000L 1,500000000000000000000000000000

7 record(s) selected.

Koostefunktioita saa käyttää vain SELECT-lauseen tuloskenttäosassa tai HAVING-lauseessa, mutta ei koskaan WHERE-lauseessa.

Lasketaan keskiarvo niiden opiskelijoiden TIE160-kurssin tenttisuorituksista, jotka ovat yrittäneet useammin kuin kerran:

SELECT opiskelija, AVG(arvosana) AS keskiarvo
FROM tenttii
WHERE kurssi = 'TIE160'
GROUP BY opiskelija
HAVING COUNT(arvosana)>= 1;
OPISKELIJA KESKIARVO
----------- ---------------------------------
010101-011P 1,250000000000000000000000000000
111111-111P 2,000000000000000000000000000000
111112-111P 3,000000000000000000000000000000
111113-111P 2,750000000000000000000000000000
111114-111P 2,500000000000000000000000000000
121212-112P 1,750000000000000000000000000000
230173-000L 1,500000000000000000000000000000

7 record(s) selected.

Lasketaan keskiarvo niiden tenttisuorituksista, jotka ovat yrittäneet vähintään 2 kertaa. Haun tulos järjestetään keskiarvon mukaan siten, että suurimman keskiarvon saanut tulee ensimmäiseksi (laskeva järjestys):

SELECT opiskelija, AVG(arvosana) as Keskiarvo
FROM tenttii
GROUP BY opiskelija
HAVING COUNT(arvosana)>= 2
ORDER BY Keskiarvo DESC;
OPISKELIJA KESKIARVO
----------- ---------------------------------
111114-111P 2,142857142857142857142857142857
111112-111P 2,000000000000000000000000000000
230173-000L 2,000000000000000000000000000000
121212-112P 1,800000000000000000000000000000
111111-111P 1,600000000000000000000000000000
111113-111P 1,500000000000000000000000000000
010101-011P 1,400000000000000000000000000000

7 record(s) selected.

Lasketaan arvosanojen keskiarvo, paras arvosana, sekä keskiarvon ja parhaan arvosanan erotus niiden opiskelijoiden tenttisuorituksista, jotka ovat yrittäneet vähintään 2 kertaa. Haun tulos järjestetään keskiarvon mukaan siten, että pienimmän keskiarvon saanut tulee ensimmäiseksi.

SELECT opiskelija, MAX(arvosana) as Paras, AVG(arvosana) as Keskiarvo, MAX(arvosana) - AVG(arvosana) as Erotus
FROM tenttii
GROUP BY opiskelija
HAVING COUNT(arvosana)>= 2
ORDER BY Keskiarvo ASC;

OPISKELIJA PARAS KESKIARVO EROTUS

----------- ----- --------------------------------- ---------------------------------

010101-011P 2,25 1,400000000000000000000000000000 0,850000000000000000000000000000

111113-111P 3,00 1,500000000000000000000000000000 1,500000000000000000000000000000

111111-111P 3,00 1,600000000000000000000000000000 1,400000000000000000000000000000

121212-112P 2,75 1,800000000000000000000000000000 0,950000000000000000000000000000

111112-111P 3,00 2,000000000000000000000000000000 1,000000000000000000000000000000

230173-000L 2,50 2,000000000000000000000000000000 0,500000000000000000000000000000

111114-111P 3,00 2,142857142857142857142857142857 0,857142857142857142857142857143

7 record(s) selected.

Käyttäjien kommentit

Kommentoi tätä sivua Lisää uusi kommentti
Kurssimateriaalien käyttäminen kaupallisiin tarkoituksiin tai opetusmateriaalina ilman lupaa on ehdottomasti kielletty!
http://appro.mit.jyu.fi/doc/tiedonhallinta/sql/dml/index13.html
© Antti Ekonoja (anjoekon@jyu.fi) <http://users.jyu.fi/~anjoekon/>
Tommi Lahtonen (tommi.j.lahtonen@jyu.fi) <http://hazor.iki.fi/>
Jukka Mäntylä (jmantyla@iki.fi) <http://www.iki.fi/jmantyla/>
2004-11-05 15:25:08
Informaatioteknologia - Jyväskylän yliopiston IT-tiedekunta ja avoin yliopisto