MAX(COUNT(*))?
Ik had vandaag op school toets SQL. Ging allemaal hartstikke lekker, behalve een vraag. Het ging om een database met allerlei films, acteurs, regisseurs, etc. etc. De vraag was als volgt: welke regisseur regisseerde de meeste films?
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
FILMS
----------------
id | dirnr | titel |
----------------
REGISSEURS
----------------------------
id | voornaam | achternaam |
----------------------------
----------------
id | dirnr | titel |
----------------
REGISSEURS
----------------------------
id | voornaam | achternaam |
----------------------------
Dat is de opbouw van de twee tabellen waar het hier om draait (films was nog uitgebreider, maar die doet er niet toe).
Nu moet ik dus de voor- en achternaam van de regisseur hebben waarbij het aantal films met zijn dirnr dus het hoogst is. Ik zat meteen aan MAX(COUNT(*)) te denken. Echter werkt deze syntaxis niet in MS Access.
Mijn query:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
SELECT a.voornaam, a.achternaam, aantal
FROM regisseurs AS a
WHERE a.nr = (SELECT b.dirnr, COUNT(b.titel) AS aantal
FROM films AS b
GROUP BY b.dirnr
HAVING COUNT(b.titel) = (SELECT MAX(COUNT(c.titel))
FROM films AS c
GROUP BY c.dirnr));
FROM regisseurs AS a
WHERE a.nr = (SELECT b.dirnr, COUNT(b.titel) AS aantal
FROM films AS b
GROUP BY b.dirnr
HAVING COUNT(b.titel) = (SELECT MAX(COUNT(c.titel))
FROM films AS c
GROUP BY c.dirnr));
Alleen die werkte dus niet... De uiteindelijke uitkomst was Steven Spielberg met 21 films. Uiteindelijk heb ik een niet zo liquide manier opgeschreven, alleen redden we het daar natuurlijk niet mee. Ik ben erg benieuwd naar wat jullie zouden geven als oplossing.
- Jezpur
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
COUNT(f.*) AS aantal,
r.voornaam,
r.achternaam
FROM
regisseurs AS r
LEFT JOIN
films AS f
ON
f.dirnr = r.id
GROUP BY
r.id,
r.voornaam,
r.achternaam
ORDER BY
aantal DESC
COUNT(f.*) AS aantal,
r.voornaam,
r.achternaam
FROM
regisseurs AS r
LEFT JOIN
films AS f
ON
f.dirnr = r.id
GROUP BY
r.id,
r.voornaam,
r.achternaam
ORDER BY
aantal DESC
pgFrank schreef op 23.06.2008 21:25:
En dirnr is hierbij het id van de regiseur? Dus de foreign key?
Jep. Dirnr in films en id in regisseurs zijn gerelateerd aan elkaar.
Edit: @ Robert: het mag maar één resultaat geven, hè...
Gewijzigd op 01/01/1970 01:00:00 door Jesper Diovo
Ps. Zet nog even een MAX() om de COUNT(), dan zou je volgens mij ook maar 1 resultaat retour krijgen. Kon ook nog wel eens sneller zijn, er hoeft niet meer te worden gesorteerd. En dat kost altijd relatief veel tijd.
Gewijzigd op 01/01/1970 01:00:00 door Frank -
pgFrank schreef op 23.06.2008 21:28:
Zie de oplossing van Robert, dat is de juiste. Wellicht wil je nog een LIMIT 1 gebruiken, dan krijg je alleen de regiseur met het hoogste aantal films retour.
LIMIT werkt niet in Access.
pgFrank schreef op 23.06.2008 21:31:
Access is dan ook een ***beep*** product. Zelfs in MySQL werkt LIMIT, dat wil wat zeggen!!! Nog zo'n hoop ***beeeeep*** waar je nooit mee wilt werken omdat het van A tot Z onbetrouwbaar is.
Ik weet het... Maar aangezien ze bij ons op school nogal uit de prehistorie stammen wat lesgeven betreft, bakken ze er hier ook niet veel van. Alle andere queries waren afschuwelijk makkelijk, dit was de enige waar ik niet uitkwam. Ik begin nu ook te begrijpen waarom :-P.
Gewijzigd op 01/01/1970 01:00:00 door Riemer
Misschien zo:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
TOP 1 COUNT(f.*) AS aantal,
r.voornaam,
r.achternaam
FROM
regisseurs AS r
LEFT JOIN
films AS f
ON
f.dirnr = r.id
GROUP BY
r.id,
r.voornaam,
r.achternaam
ORDER BY
aantal DESC
TOP 1 COUNT(f.*) AS aantal,
r.voornaam,
r.achternaam
FROM
regisseurs AS r
LEFT JOIN
films AS f
ON
f.dirnr = r.id
GROUP BY
r.id,
r.voornaam,
r.achternaam
ORDER BY
aantal DESC
Hmmm... daarom dus nooit MS access gebruiken om SQL te leren!!
Robert_Deiman schreef op 23.06.2008 21:34:
LIMIT werkt niet in ACCESS?
Misschien zo:
Hmmm... daarom dus nooit MS access gebruiken om SQL te leren!!
Misschien zo:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
TOP 1 COUNT(f.*) AS aantal,
r.voornaam,
r.achternaam
FROM
regisseurs AS r
LEFT JOIN
films AS f
ON
f.dirnr = r.id
GROUP BY
r.id,
r.voornaam,
r.achternaam
ORDER BY
aantal DESC
TOP 1 COUNT(f.*) AS aantal,
r.voornaam,
r.achternaam
FROM
regisseurs AS r
LEFT JOIN
films AS f
ON
f.dirnr = r.id
GROUP BY
r.id,
r.voornaam,
r.achternaam
ORDER BY
aantal DESC
Hmmm... daarom dus nooit MS access gebruiken om SQL te leren!!
Hm, dat ziet er interessant uit. Alleen kregen we een bijlage erbij met alle statements, en daar stond TOP niet bij, dus kan niet geloven dat dit echt de oplossing is die bedoeld wordt, maar ik geloof graag dat 'ie werkt :-).
TOP
pgFrank schreef op 23.06.2008 21:39:
MS heeft natuurlijk ook nog SQL Server, toch wel een redelijk product. Ik heb er nooit zelf mee gewerkt, maar toch wel aardig wat query-werk voor opgeleverd. De klanten zijn er altijd blij mee geweest, ik kan blijkbaar aardig raden hoe het moet werken... Gelukkig maar!
TOP
TOP
Inderdaad, op zich wijkt de Access-SQL-syntaxis ook niet veel af hoor. Sommige dingen zijn alleen nét iets anders. Zoals LIMIT, en volgens mij hebben de WHERE, GROUP BY, HAVING en ORDER BY ook een andere volgorde. Maar daar valt nog over te twisten.
In ieder geval bedankt, weer iets geleerd (TOP! :-P). Misschien is dat nog wel eens handig voor de toekomst. We wachten de uitslag van de toets in ieder geval af :-).