meerdere counts als één resultaat

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Veur Heur

Veur Heur

21/02/2022 14:08:39
Quote Anchor link
Een tijd geleden postte ik het volgende: https://www.phphulp.nl/php/forum/topic/database-model/102835/

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)
PHP script in nieuw venster Selecteer het PHP script
1
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;


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?
 
PHP hulp

PHP hulp

19/08/2022 00:53:51
 
Ad Fundum

Ad Fundum

21/02/2022 16:41:09
Quote Anchor link
Ja, dat kan.

(
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.
)
 
Veur Heur

Veur Heur

21/02/2022 16:45:21
Quote Anchor link
Oh shoot, ik had * geplaatst omdat het anders zoveel informatie zou worden, maar mijn query select op dit moment alleen "pretparken.pretparkid, pretparken.pretpark, ritten.datum, DATE_FORMAT(ritten.datum, "%d-%m-%Y") AS datum_formatted, COUNT(ritten.achtbaanid) AS count_achtbanen", m.a.w. ik doe al een COUNT, maar zodra ik de pretparkid uit de GROUP BY zou halen, krijg ik wel het juiste aantal, maar slechts één park ipv beide (of nog meer) parken.
 
Ivo P

Ivo P

21/02/2022 17:07:23
Quote Anchor link
misschien handig om je query even tussen code] tags te zetten en op goede plekken op Enter te drukken.
Daarmee wordt je query mogelijk wel leesbaar...
Gewijzigd op 21/02/2022 17:08:46 door Ivo P
 
Ad Fundum

Ad Fundum

21/02/2022 20:51:31
Quote Anchor link
Je vroeg:
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
 
Ivo P

Ivo P

21/02/2022 21:33:51
Quote Anchor link
mogelijk zoek je naar grouping() en with-rollup.

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.
 
Veur Heur

Veur Heur

21/02/2022 22:12:10
Quote Anchor link
Dank voor de tips tot dusver, morgen eerst eens kijken naar de versie van MySQL op m’n server, denk dat die nog te oud is, althans als ik snel kijk naar de links zie ik 8.0 staan, mag ik er dan van uitgaan dat het met v5 niet gaat lukken?
 
Ivo P

Ivo P

21/02/2022 22:13:57
 
Veur Heur

Veur Heur

21/02/2022 22:14:58
Quote Anchor link
Top, dank Ivo, ik duik eens in de mogelijkheden.

Toevoeging op 22/02/2022 07:28:59:

Onderstaande levert precies dat wat ik zocht:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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


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)
PHP script in nieuw venster Selecteer het PHP script
1
GROUP_CONCAT(DISTINCT(pretparken.pretpark) ORDER BY pretpark ASC SEPARATOR "|") AS pretparken
Gewijzigd op 22/02/2022 07:46:57 door Veur Heur
 
Ivo P

Ivo P

22/02/2022 13:37:02
Quote Anchor link
maar als die query geen foutmelding geeft

welke naam krijg je dan naast die lijst met pretparken?

maw je groupby mist velden
 
Veur Heur

Veur Heur

22/02/2022 13:40:36
Quote Anchor link
Voor de volledigheid, op dit moment gebruik ik deze query en die geeft geen foutmelding:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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


Je vraag is me dan ook niet duidelijk.
Gewijzigd op 22/02/2022 13:43:07 door Veur Heur
 
Ivo P

Ivo P

22/02/2022 16:05:33
Quote Anchor link
Dit is natuurlijk een andere query:
de vorige query die je postte, bevat de regels.

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
SELECT
  pretparken.pretparkid,
  pretparken.pretpark,


En dat gaat dan niet goed
 
Veur Heur

Veur Heur

22/02/2022 16:09:28
Quote Anchor link
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.
 
Ivo P

Ivo P

22/02/2022 16:26:23
Quote Anchor link
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.
 
Veur Heur

Veur Heur

22/02/2022 16:54:27
Quote Anchor link
Voor deze query is het niet van belang waar de ritten waren, ik ben op zoek naar het totaal van de dag en niet het totaal per pretpark. Door GROUP_CONCAT weet ik echter wél welke parken betrekking hebben op de dag, alleen niet hoeveel per park, maar die informatie heb ik niet nodig.
 
Ad Fundum

Ad Fundum

22/02/2022 22:17:05
Quote Anchor link
Fijn dat het gelukt is met GROUP_CONCAT().
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.