meerdere counts als één resultaat
Inmiddels is dit project flink uitgebreid, maar de basis is hetzelfde:
- pretparken: pretparkid, pretpark
- achtbanen: achtbaanid, achtbaan
- ritten: ritid, achtbaanid, pretparkid, datum, aantal
Het dashboard heeft een aantal milestones waaronder "meeste achtbanen op één dag" en "Meeste ritten op één dag". Nu komt het (heel sporadisch) wel eens voor dat we meerdere parken op één dag bezoeken. Deze zou ik dan samen willen voegen. Op dit moment doe ik dat zo:
Code (php)
1
2
3
4
5
2
3
4
5
SELECT *
FROM ritten
INNER JOIN pretparken ON ritten.pretparkid=pretparken.pretparkid
GROUP BY ritten.datum, pretparken.pretparkid
ORDER BY count_achtbanen DESC, ritten.datum ASC, pretparken.pretpark ASC LIMIT 0,10;
FROM ritten
INNER JOIN pretparken ON ritten.pretparkid=pretparken.pretparkid
GROUP BY ritten.datum, pretparken.pretparkid
ORDER BY count_achtbanen DESC, ritten.datum ASC, pretparken.pretpark ASC LIMIT 0,10;
Dit levert een lijst met de 10 dagen waarop we de meeste verschillende achtbanen bereden, maar gaat daarbij voorbij aan dagen waarop we 7 achtbanen in het ene en 3 achtbanen in het andere park deden, waardoor deze dag mogelijk in de top 10 zou kunnen komen.
Zou het mogelijk zijn de query aan te passen zodat deze de waardes "meerdere parken" en "ritten opgeteld" bevat?
(
Dan moet je SUM(aantal) gebruiken in SELECT, en pretparken.pretparkid uit GROUP BY halen.
Eventueel kan je SUM(aantal) DESC gebruiken in ORDER BY.
)
Daarmee wordt je query mogelijk wel leesbaar...
Gewijzigd op 21/02/2022 17:08:46 door Ivo P
Quote:
Zou het mogelijk zijn de query aan te passen zodat deze de waardes "meerdere parken" en "ritten opgeteld" bevat?
Logisch gezien kan je slechts het totaal aantal ritten geven:
- per park
- per dag
- per park per dag
Wil je het toch per dag, maar ook zien bij welke park(en) dat was, dan gebruik je GROUP_CONCAT(pretpark).
https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat
https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html
geeft een aantal voorbeelden. Maar je moet daarbij wel heel erg opletten of je query echt klopt en welk record je moet hebben voor welke sommatie.
Toevoeging op 22/02/2022 07:28:59:
Onderstaande levert precies dat wat ik zocht:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
SELECT
pretparken.pretparkid,
pretparken.pretpark,
ritten.datum,
DATE_FORMAT(ritten.datum, "%d-%m-%Y") AS datum_formatted,
COUNT(ritten.achtbaanid) AS count_achtbanen,
GROUP_CONCAT(DISTINCT(pretparken.pretpark)) AS pretparken
FROM ritten
INNER JOIN pretparken ON ritten.pretparkid=pretparken.pretparkid
GROUP BY ritten.datum
ORDER BY count_achtbanen DESC, ritten.datum ASC, pretparken.pretpark ASC
LIMIT 0,10
pretparken.pretparkid,
pretparken.pretpark,
ritten.datum,
DATE_FORMAT(ritten.datum, "%d-%m-%Y") AS datum_formatted,
COUNT(ritten.achtbaanid) AS count_achtbanen,
GROUP_CONCAT(DISTINCT(pretparken.pretpark)) AS pretparken
FROM ritten
INNER JOIN pretparken ON ritten.pretparkid=pretparken.pretparkid
GROUP BY ritten.datum
ORDER BY count_achtbanen DESC, ritten.datum ASC, pretparken.pretpark ASC
LIMIT 0,10
Hierbij is het resultaat van "pretparken" een komma gescheiden waarde met de betreffende pretparken.
Toevoeging op 22/02/2022 07:46:08:
Aangezien het goed mogelijk is dat er een komma in de naam van het pretpark zal zitten op den duur, heb ik ervoor gekozen een andere separator toe te passen en daarnaast heb ik een sortering toegevoegd:
Code (php)
1
GROUP_CONCAT(DISTINCT(pretparken.pretpark) ORDER BY pretpark ASC SEPARATOR "|") AS pretparken
Gewijzigd op 22/02/2022 07:46:57 door Veur Heur
welke naam krijg je dan naast die lijst met pretparken?
maw je groupby mist velden
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT
GROUP_CONCAT(DISTINCT(pretparken.pretpark) ORDER BY pretpark ASC SEPARATOR "|") AS pretparken,
ritten.datum,
DATE_FORMAT(ritten.datum, "%d-%m-%Y") AS datum_formatted,
COUNT(ritten.achtbaanid) AS count_achtbanen
FROM ritten
INNER JOIN pretparken ON ritten.pretparkid=pretparken.pretparkid
GROUP BY ritten.datum
ORDER BY count_achtbanen DESC, ritten.datum ASC
LIMIT 0,10
GROUP_CONCAT(DISTINCT(pretparken.pretpark) ORDER BY pretpark ASC SEPARATOR "|") AS pretparken,
ritten.datum,
DATE_FORMAT(ritten.datum, "%d-%m-%Y") AS datum_formatted,
COUNT(ritten.achtbaanid) AS count_achtbanen
FROM ritten
INNER JOIN pretparken ON ritten.pretparkid=pretparken.pretparkid
GROUP BY ritten.datum
ORDER BY count_achtbanen DESC, ritten.datum ASC
LIMIT 0,10
Je vraag is me dan ook niet duidelijk.
Gewijzigd op 22/02/2022 13:43:07 door Veur Heur
de vorige query die je postte, bevat de regels.
Code (php)
1
2
3
2
3
SELECT
pretparken.pretparkid,
pretparken.pretpark,
pretparken.pretparkid,
pretparken.pretpark,
En dat gaat dan niet goed
Veur Heur op 22/02/2022 16:09:28:
Daar heb je gelijk in, die waardes gaven echter geen problemen. Ze waren overbodig en leverden in feite gewoon één van de resultaten op die ook in het gegroepeerde resultaat staan.
Ja, maar dat is dus een zinloos resultaat: je krijgt er 1. Welke weet je niet en is afhankelijk van bijvoorbeeld de volgorde van invoeren. (van de ritten of van de parken?)
En in deze is het een telling die betrekking heeft op alle ritten in die parken, dus ook op al die parken.
In de tutorial sectie op phphulp staat een voorbeeld van een MAX(). (prijs) daarbij wordt ook een willekeurige naam getoond, maar die heeft geen betrekking op de max-prijs.
Helaas accepteert MySQL dit soort query's onder het mom: beter een resultaat geven dat ongeveer klopt en mogelijk is wat de gebruiker nodig had, dan een foutmelding.
Gelukkig kun je dat gedrag afdwingen middels ONLY_FULL_GROUP_BY
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
en
https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html
De pagina over versie 5.7 meldt nog:
Quote:
(Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default.
Op die pagina wordt ook middels een voorbeeld precies het probleem beschreven met jouw pretparknaam. Dat is in dat voorbeeld "abc"
Speel die query's maar eens na. En zeker als je speelt met het stuk WHERE, zul je zien dat er gekke resultaten uit kunnen komen.
Daarmee zou je zo maar kunnen denken dat je 100 ritten in de Efteling deed, terwijl dat er maar 1 was en de andere 99 verdeeld over Walibi en Plopsa waren.