GROUP BY , SUM, FREQUENTIE
merk
-------------
nokia
samsung
nokia
sony
sony
samsung
nokia
samsung
lg
lg
nu wil ik het volgende:
het aantal keer dat een merk voorkomt berekenen, gaat met
SELECT merk,count(merk) FROM `re_monograms` GROUP BY merk
nu wil ik deze frequenties weer optellen, zodat ik aan een totaal aantal kom, ik zou verwachten dat dit gaat met
SELECT sum(count(merk)) FROM `re_monograms` GROUP BY merk
maar helaas niet, hoe tel ik de frequentie resultaten op?
een stap verder is weer dat ik dus de relatieve frequentie wil bereken, dus het aantal keer dat bijvoorbeeld nokia voorkomt, dat zou ik dit geval dus 0,3 moeten opleveren, hoe doe ik dat?
Gewijzigd op 01/01/1970 01:00:00 door Femke
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT
merk,
COUNT(merk) AS aantal
FROM
re_monograms
GROUP BY
merk
merk,
COUNT(merk) AS aantal
FROM
re_monograms
GROUP BY
merk
En
Code (php)
1
2
3
4
2
3
4
SELECT
COUNT(merk) AS aantal
FROM
re_monograms
COUNT(merk) AS aantal
FROM
re_monograms
Ga je eens verdiepen in het gebruik van GROUP BY, dan snap je zelf ook waarom je dit niet in 1 query kunt oplossen.
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
SELECT
merk,
COUNT(merk) AS aantal
FROM
re_monograms
GROUP BY
merk
WITH ROLLUP
merk,
COUNT(merk) AS aantal
FROM
re_monograms
GROUP BY
merk
WITH ROLLUP
Het enige waar je wel een aparte query voor nodig hebt is in dit geval de relatieve frequentie.
ps. Referentie naar de MySQL handleiding: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html
Gewijzigd op 01/01/1970 01:00:00 door Joren de Wit
Edit:
Ik lees op de website van MySQL dat er extra rijen met een NULL value voor de merknaam toegevoegd worden, waarin de running totals geplaatst worden.
Gewijzigd op 01/01/1970 01:00:00 door Jan Koehoorn
Ik weet alleen niet of het wel een goed plan is om deze te gebruiken, ik zou hem eerst eens uitgebreid gaan testen.
Eén van de voorbeelden op de site:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT year, country, product, SUM(profit)
-> FROM sales
-> GROUP BY year, country, product WITH ROLLUP
-> LIMIT 5;
+------+---------+------------+-------------+
| year | country | product | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | NULL | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
+------+---------+------------+-------------+
-> FROM sales
-> GROUP BY year, country, product WITH ROLLUP
-> LIMIT 5;
+------+---------+------------+-------------+
| year | country | product | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | NULL | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
+------+---------+------------+-------------+
Ben ook benieuwd wat ORDER BY voor resultaten gaat opleveren. Ik voorzie in elk geval problemen met de presentatie wanneer de gegevens doorelkaar staan.
Gewijzigd op 01/01/1970 01:00:00 door Jan Koehoorn
Het is zelfs zo, dat het totaal van Finland hiermee ook waardeloos wordt, je hebt niks om mee te vergelijken.
Kortom, je wilt zowel het totaal per product opvragen als het totaal per land, maar het totaal per land krijg je alleen wanneer er niet te veel producten in de lijst staan. Die is lekker, dan weet je dus nooit zeker wat je terug gaat krijgen.
Kortom, gebruik 2 queries, deze functie is niet te vertrouwen. Met 2 queries weet je precies wat je terug gaat krijgen, ook als je een LIMIT gebruikt of gaat sorteren.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> SELECT year, country, product, SUM(profit)
-> FROM sales
-> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | NULL | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | India | NULL | 1350 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2000 | USA | NULL | 1575 |
| 2000 | NULL | NULL | 4525 |
| 2001 | Finland | Phone | 10 |
| 2001 | Finland | NULL | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
| 2001 | USA | NULL | 3000 |
| 2001 | NULL | NULL | 3010 |
| NULL | NULL | NULL | 7535 |
+------+---------+------------+-------------+
-> FROM sales
-> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | NULL | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | India | NULL | 1350 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2000 | USA | NULL | 1575 |
| 2000 | NULL | NULL | 4525 |
| 2001 | Finland | Phone | 10 |
| 2001 | Finland | NULL | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
| 2001 | USA | NULL | 3000 |
| 2001 | NULL | NULL | 3010 |
| NULL | NULL | NULL | 7535 |
+------+---------+------------+-------------+
Volgens mij moet je er du erg voorzichtig mee zijn
Dus ik zie toch wel voordelen
en inderdaad geen LIMIT
Er staan verschillende soort data doorelkaar waardoor je dit probleem krijgt. Wederom een probleem met de GROUP BY van MySQL.
Het lijkt leuk, maar de output is afhankelijk van de gegevens die jij al hebt. Jij moet bv. al weten dat Finland en India ieder 2 records met producten hebben en ieder nog 1 record met het totaal heeft (per land dus 3 records). Wanneer je er dan voor zorgt dat je LIMIT 6 gebruikt (2x3), krijg je de juiste data retour. Dit slaat toch nergens op, of wel dan?
Query: Per land per product wil ik het totaal weten en per land het overall totaal. Beperk de resultaten tot 5 records. Welke gegevens ga jij dan retour krijgen? Dat weet je dus niet, gelukkig heeft MySQL wel weer een fijne verrassing voor jou in petto...
Met 2 aparte queries weet je precies welke resultaten, welke data, jij retour krijgt. De totalen per land per product, of het totaal per land. En dat kun je prima met een LIMIT aanpakken.
Het blijft zoals jij ooit zei "het debiele broertje :)'
1) 1 record met product van land A, het totaal van land A, 1 record met product van land B, het totaal van land B en 1 record met product van land C. totaal van land C ontbreekt. (fout)
2) 2 records met producten van land A, het totaal van land A, 2 records met product van land B, het totaal van land B ontbreekt. (fout)
3) 2 records met producten van land A, het totaal van land A, 1 record met product van land B en het totaal van land B. (goed)
4) 3 records met producten van land A, het totaal van land A, 1 record met product van land B, het totaal van land B ontbreekt (fout)
5) 4 records met producten van land A en het totaal van land A (goed)
6) 5 records met producten van land A, totaal ontbreekt (fout)
Zoals je kunt zien, levert deze query in slechts 2 van de 6 gevallen een correct (lees: volledig) resultaat op. In de andere gevallen ontbreken er gegevens en je kunt er slechts naar raden wát er ontbreekt.
Ik vraag me toch werkelijk af wat het doel van MySQL is met dit soort belabberde toepassingen.
Maar goed, PostgreSQL kost niets en is vele malen handiger in het gebruik. ;)
Gewijzigd op 01/01/1970 01:00:00 door Frank -
Het enige leuke waar deze functie van pas zou komen is als je groepeert op 1 kolom. Je weet dan namelijk altijd zeker dat het laatste record uit je result set de running total van alle voorgaande records bevat.
Maar Frank, ik ben het helemaal met je eens dat het niet netjes is dat er onvolledige resultaten weergegeven worden. En zoals je zelf ook al aandraagt gaat dit voor problemen zorgen zodra je groepeert op meerdere kolommen. Een uitgebreide inspectie van de resultaten met php is dan nodig om erachter te komen welke waarde wat voorstelt en dat weegt niet op tegen de extra code voor een tweede query.
Klaasjan Boven schreef op 29.01.2007 22:02:
TOch heb ik de indruk dat dit (met name de laatste drie rijen) ook willekeurige output is
Volgens mij moet je er du erg voorzichtig mee zijn
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> SELECT year, country, product, SUM(profit)
-> FROM sales
-> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | NULL | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | India | NULL | 1350 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2000 | USA | NULL | 1575 |
| 2000 | NULL | NULL | 4525 |
| 2001 | Finland | Phone | 10 |
| 2001 | Finland | NULL | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
| 2001 | USA | NULL | 3000 |
| 2001 | NULL | NULL | 3010 |
| NULL | NULL | NULL | 7535 |
+------+---------+------------+-------------+
-> FROM sales
-> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | NULL | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | India | NULL | 1350 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2000 | USA | NULL | 1575 |
| 2000 | NULL | NULL | 4525 |
| 2001 | Finland | Phone | 10 |
| 2001 | Finland | NULL | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
| 2001 | USA | NULL | 3000 |
| 2001 | NULL | NULL | 3010 |
| NULL | NULL | NULL | 7535 |
+------+---------+------------+-------------+
Volgens mij moet je er du erg voorzichtig mee zijn
De laatste records zijn ook duidelijk, mijns insziens. Niets niet "willekeurige output".
In 2001 waren er 50 calculators, 2700 computers en 250 TV's verkocht. Totaal (USA | NULL) is 3000
In 2001 waren er in Finland alleen maar 10 Phones verkocht. Totaal in 2001 is dus 3010. (2001 |NULL |NULL)
En het laatste record is het totaal van 2000 (2000 | NULL | NULL | = 4525) plus het totaal van 2001 (wat ik net al zei 3010) dus dat is 7535 en dat is het totaal aantal gevonden records.
Of het misgaat als je GROUP BY doet op meerdere kolommen weet ik niet.
Maar waar het probleem ligt is dat je niet weet welke waarde wat precies voorstelt. Met andere woorden, je moet voordat je zo'n query uitvoert eigenlijk al precies weten wat de uitkomst gaat zijn. Je moet wel zeker weten dat het totaal in bovenstaande op regel 24 weergegeven wordt, en daar komt in ieder geval nog wat php code aan te pas.
Je moet namelijk gaan controleren of de velden year, country en/of product NULL zijn, en aan de hand daarvan moet je gaan bepalen welke waarde er in dat record weergegeven wordt.
Dat klopt, maar ik wou het toch even zeggen. Omdat er anders misverstanden over kunnen ontstaan, of het nu wel of niet doet waarvoor het is bedoeld. En dat doet het dus wel.
Ik snap dat het probleem daar ligt, dat je niet weet welke regel welk resultaat heeft.
Het ging mij er meer om dat de functie in sommige gevallen handig kan zijn, maar als je ervanuit gaat dat het "willekeurig resultaat" teruggeeft, dan zou je het niet durven gebruiken.:)