[SQL] rekenen in grouped result set

[Verwijderd]

Uitgaande van de volgende tabel, als voorbeeld:

id	huis_id	deuren_aantal	deuren_prijs_totaal
1	1	2	$20.00
2	1	2	$25.00
3	2	3	$30.00
4	2	4	$15.00

Op deze (fictieve) data set laat ik een query los die gegroepeerd is op huis_id (GROUP BY `huis_id` - omdat er in de daadwerkelijke tabel nog veel meer datavelden staan waar de sommen van benodigd zijn).
Nu wil ik tevens in die result set de gemiddelde kostprijs per deur hebben.
In ons voorbeeld zouden we twee rijen geretourneerd krijgen met de volgende query;

code:

SELECT `huis_id`, COUNT(`id`) AS `aantal_rows`
FROM `tablename`
GROUP BY `huis_id`
ORDER BY `huis_id` ASC

Is er een eenvoudige methode - die ik over het hoofd zie - waarmee in die result set tevens de gemiddelde prijs van een deur opgenomen kan zijn, van de betreffende `huis_is` groep?
Met andere woorden, is er een query waarmee ik de hierna volgende result set kan verkrijgen? Of moet ik noodgedwongen met twee queries gaan werken?

Beoogde result set:

huis_id	aantal_rows	avg prijs per deur
1	2	$11.25
2	2	$6.875

Of is het verstandig om de gemiddelde prijs per deur als extra veld op te nemen in de tabel, zodat die waarde reeds bekend zal zijn per row?

Note: uiteraard zou je er in dit voorbeeld ook voor kunnen kiezen om de prijs per deur op te slaan in je tabel, in plaats van de totaal prijs. Dan zou het eenvoudig op te lossen zijn. Echter gaat het mij om de vraag of het mogelijk is om PER RIJ een berekening uit te voeren, en het gemiddelde van die UITKOMST op te nemen in je GROUPED result set...

[ Voor 12% gewijzigd door Verwijderd op 07-11-2009 19:02 . Reden: opmaak foutje... ]

[Janoz]

Naast count, sum, min en max heeft SQL ook nog de AVG als aggregerende functie.

O wacht, er zit al een aantal in.

Wat je ook kunt doen is de sum van de deuren en de sum van de prijs te nemen en dan vervolgens in je applicatie zelf deze twee op elkaar delen.

[ Voor 53% gewijzigd door Janoz op 07-11-2009 18:57 ]

[Verwijderd]

Janoz schreef op zaterdag 07 november 2009 @ 18:54:
Naast count, sum, min en max heeft SQL ook nog de AVG als aggregerende functie.

Dat is me bekend.
Heb je mijn edits ook gezien? Ik wil dus per rij (die later deel uit zal maken van een group) een berekening uitvoeren en het gemiddelde verkrijgen van de uitkomst van die berekeningen. Met andere woorden, de waarden waarvan het gemiddelde benodigd is zijn niet bekend in de tabel maar dienen te worden verkregen door een berekening uit te voeren?

[Verwijderd]

Verwijderd, RobertMe lijkt de oplossing te geven...

[ Voor 88% gewijzigd door Verwijderd op 07-11-2009 19:03 ]

[RobertMe]

AVG(deuren_prijs_totaal / deuren_aantal) ?
Wat je tussen de aggregate functies zet (als argument mee geeft) word gewoon per rij berekend en niet per groep.

[Verwijderd]

RobertMe schreef op zaterdag 07 november 2009 @ 19:02:
AVG(deuren_prijs_totaal / deuren_aantal) ?
Wat je tussen de aggregate functies zet (als argument mee geeft) word gewoon per rij berekend en niet per groep.

Bedankt! Ik was me er niet van bewust dat die berekening per rij berekend wordt.

[Janoz]

RobertMe's oplossing is niet de juiste. In zijn oplossing telt elk type even zwaar mee, onafhankelijk van de hoeveelheid. Bij 10 deuren van $10 en 5 van $20 komt uit zijn oplossing $15 rollen, terwijl het in werkelijkheid $13,33 zou moeten zijn ($200 / 15)

[RobertMe]

Janoz schreef op zaterdag 07 november 2009 @ 19:19:
RobertMe's oplossing is niet de juiste. In zijn oplossing telt elk type even zwaar mee, onafhankelijk van de hoeveelheid. Bij 10 deuren van $10 en 5 van $20 komt uit zijn oplossing $15 rollen, terwijl het in werkelijkheid $13,33 zou moeten zijn ($200 / 15)

Deze beredenering klopt niet, zijn verwachte antwoord is wel 15, want als ik jouw reken manier toepas op zijn voorbeeld van totaalprijs: $30 voor 3 deuren en $15 voor 4 deuren, is het antwoord $6,429 (45/7) terwijl zijn verwachte antwoord $6.875 is.

[pedorus]

Dit geeft wel een beetje een vertekend beeld natuurlijk. Ik zou persoonlijk denken dat de prijs per deur in huis 2 45/7=6.429 is, en dat de gemiddelde deurprijs per batch(=rij) minder interessant is voor verdere berekeningen... Zoals Janoz voorstelt dus, hoewel 'niet de juiste' afhangt van wat je er verder mee wil.

[Janoz]

RobertMe schreef op zaterdag 07 november 2009 @ 19:33:
[...]

Deze beredenering klopt niet, zijn verwachte antwoord is wel 15, want als ik jouw reken manier toepas op zijn voorbeeld van totaalprijs: $30 voor 3 deuren en $15 voor 4 deuren, is het antwoord $6,429 (45/7) terwijl zijn verwachte antwoord $6.875 is.

Zijn vraag is "Nu wil ik tevens in die result set de gemiddelde kostprijs per deur hebben." Dat hij daarin zelf een rekenfout maakt doet daar niks aan af lijkt me.

[CMG]

UNION
SELECT -1 as huis_id, COUNT(*) as aantal_rows, AVG() as [avg prijs per deur]

[nescafe]

Of de deelt de totale deurprijs door het totaal aantal deuren, waarbij je zelf kunt bepalen waarop je groepeert:

SQL:

SELECT huis_id, COUNT(*) AS aantal_rows,
  SUM(deuren_prijs_totaal) / NULLIF(SUM(deuren_aantal), 0) AS PrijsPerDeur
FROM tablename
GROUP BY huis_id
ORDER BY huis_id ASC

Met de NULLIF sluit je een deling door nul (0) uit, weet niet of jouw dbms dit kent, anders moet je het oplossen met een case- of een if-statement..

[ Voor 7% gewijzigd door nescafe op 07-11-2009 20:31 ]

[CMG]

nescafe schreef op zaterdag 07 november 2009 @ 20:30:
Of de deelt de totale deurprijs door het totaal aantal deuren, waarbij je zelf kunt bepalen waarop je groepeert:

SQL:

SELECT huis_id, COUNT(*) AS aantal_rows,
  SUM(deuren_prijs_totaal) / NULLIF(SUM(deuren_aantal), 0) AS PrijsPerDeur
FROM tablename
GROUP BY huis_id
ORDER BY huis_id ASC

Met de NULLIF sluit je een deling door nul (0) uit, weet niet of jouw dbms dit kent, anders moet je het oplossen met een case- of een if-statement..

Volgens mij bedoel je ISNULL, niet NULLIF?

http://blog.sqlauthority....son-of-nullif-and-isnull/

[Verwijderd]

Janoz schreef op zaterdag 07 november 2009 @ 20:02:
[...]


Zijn vraag is "Nu wil ik tevens in die result set de gemiddelde kostprijs per deur hebben." Dat hij daarin zelf een rekenfout maakt doet daar niks aan af lijkt me.

Je kunt het een rekenfout noemen; ik ben dat echter niet met je eens. Je kunt het probleem namelijk op twee manieren benaderen. Als je uitgaat van de data in mijn TS dan zie je dat er in huis 2 tweemaal een deur/prijs verhouding staat vermeld (3 deuren voor $30 en 4 deuren voor $15). Je kunt nu stellen dat de gemiddelde prijs per deur ($45 / 7 deuren = ) $6,429 is - de methode waar Janoz vanuit lijkt te gaan.
Echter kun je ook eerst de prijs die per deur is betaald berekenen (berekenen per individuele rij) waardoor je op een gemiddelde prijs per deur van ((($30 / 3) + ($15 / 4)) / 2 = ) $6,875 komt, zoals RobertMe bereikt, en waar ik naar op zoek was. Met andere woorden, er zijn twee wegen te bewandelen die overigens beide niet foutief of goed te noemen zijn. Dat hangt namelijk van de situatie af. Zoals genoemd ben ik op zoek naar de tweede methode (met een resultaat van $6,875) en die is te bereiken met de uitleg van RobertMe.
Nogmaals mijn dank daarvoor.

[ Voor 6% gewijzigd door Verwijderd op 08-11-2009 00:09 ]

[pedorus]

Toch vraag ik me wel een beetje af wat de situatie hier dan is, want de kans is nogal groot dat je eigenlijk sum(a)/sum(b) wilt ipv sum(a/b).... Er zijn wel speciale situaties waar je sum(a/b) wilt, maar dan moet je bijvoorbeeld denken aan het snel kunnen zien van effecten in een tijdreeks. Je komt ook niet op "een gemiddelde prijs per deur" zoals je zegt, maar op een gemiddelde prijs die per batch voor een deur betaald is. Dat is gewoon niet hetzelfde.

CMG schreef op zaterdag 07 november 2009 @ 21:59:
Volgens mij bedoel je ISNULL, niet NULLIF?

Hij bedoelt wel NULLIF en niet ISNULL/IFNULL/COALESCE denk ik, hoewel ik het sowieso achterwege zou laten.

[Janoz]

@Ivy2009
Met ale respect hoor, maar je definitie slaat wiskundig gezien helemaal nergens op. Laat ik de situatie eens in het extreme trekken om je hopelijk in te laten zien dat de door jou berekende waarde helemaal niks zegt. Stel ik bouw een hotel met 1000 gewone deuren van $10 en een hele grote luxe voordeur van $1000. Als iemand vervolgens aan mij vraagt wat de gemiddelde deurprijs is, denk je dan dat hij $550 wil horen?

Of anders. Jij verteld iemand je waarde en het aantal deuren dat er in het huis zit. In je tweede geval is dat 7 deuren met een gemiddelde deurprijs van $6,875. Het lijkt me dan helemaal niet vreemd dat diegene dan verwacht dat hij $48,125 voor de deuren moet gaan betalen, en niet de $45 die het werkelijk is.

[nescafe]

CMG schreef op zaterdag 07 november 2009 @ 21:59:
[...]


Volgens mij bedoel je ISNULL, niet NULLIF?

http://blog.sqlauthority....son-of-nullif-and-isnull/

pedorus schreef op zondag 08 november 2009 @ 00:46:

[...]

Hij bedoelt wel NULLIF en niet ISNULL/IFNULL/COALESCE denk ik, hoewel ik het sowieso achterwege zou laten.

Het doel van de NULLIF is het voorkomen van een deling door nul. Stel het veld deuren_aantal heeft voor een huis de waarde nul (0) dan heb ik liever dat er in mijn resultaat een NULL verschijnt op dat ene huis i.p.v. een error. Omdat ik niet weet of de TS deze situatie heeft afgevangen met een constraint, plaats ik er voor de zekerheid deze constructie in.

[pedorus]

quote: http://www.joelonsoftware.com/articles/Unicode.html

I think, the point that Postel's Law about being "conservative in what you emit and liberal in what you accept" is quite frankly not a good engineering principle.

Het lijkt me gek om bepaalde fouten waarvan je niet eens weet of ze op kunnen treden, en of negeren een goede afhandeling is, alvast preventief te gaan negeren. Overigens levert een deling door 0 in sommige dialecten sowieso al NULL op.