SQL: Gemiddelde berekenen van "grouped " top 5

[Verwijderd]

Ik moet het toegeven, ik ben geen SQL held. Maar dit probleem lijkt me een redelijke uitdaging, ik kom er in iedergeval niet uit.

Het volgde wil ik voor elkaar krijgen:

Ik heb een Access database met slechts 1 tabel voor 1 product. Deze tabel bevat alle orders (80.000) van een onderneming. In deze tabel staan alle order gegevens, de belangrijkste voor deze query: [Productieland], [bestemmingsland] en [hoeveelheid KG besteld per order]. Nu heb ik een query gemaakt die mij de unieke land combinaties terug geeft. Maar het lukt me niet om van deze combinaties het gemiddelde van de top 5 bestel hoeveelheid per landcombinatie te krijgen.


Tabel layout
[Productieland], [bestemmingsland] en [hoeveelheid KG besteld per order]
NL DE 10000
NL SE 10000
NL DE 30000
SE DE 20200

Gewenste tabel
Unieke land combinaties [Gem. Hoeveelheid op basis van top 5 belevering op route]
NL DE 20000
NL SE 10000
SE DE 20200

Het is een Access database.

Ik hoop dat iemand mijn vraag begrijpt en mij ook een eindje op weg wil helpen. Met behulp van Google kwam ik niet het juiste antwoord tegen.

Vriendelijk bedankt

[Verwijderd]

select produktieland, bestemmingsland, avg(*)
from tbl
group by produktieland, bestemmingsland


Ik ben ook geen SQL-held en zeker niet als ik het niet kan testen. Dit is uit de losse pols dus.
Try at your own risk

Sorry had niet de top 5 gezien

[ Voor 10% gewijzigd door Verwijderd op 31-05-2005 10:18 ]

[LuCarD]

P&W FAQ - SQL

en avg daarmee zou het moeten kunnen...

[Verwijderd]

Bedankt!

Maar ik will graag het gemiddelde van de top 5 hebben van de goederenstroom per unieke landencombinatie. Dat krijg ik op deze manier niet, denk ik...

GR

[LuCarD]

Verwijderd schreef op dinsdag 31 mei 2005 @ 10:21:
Bedankt!

Maar ik will graag het gemiddelde van de top 5 hebben van de goederenstroom per unieke landencombinatie. Dat krijg ik op deze manier niet, denk ik...

GR

je kan op de aggregate velden ook sorteren, en volgens mij heeft access top om de bovenste paar records te krijgen.


dus je krijgt iets van

select top xxx, velden from .... group by .... order by ...

[rogue]

select top 5 var, var, avg(*) from table etc zou moeten werken.
Overigens is hierover een boel informatie te vinden via Google en Access support sites.

edit:
note to self: vaker ctrl-r doen

[ Voor 14% gewijzigd door rogue op 31-05-2005 10:29 ]

[Verwijderd]

Wederom bedankt, maar nu krijg ik de top 5 resultaten van het gemiddelde, maar ik wil graag het gemiddelde voor alle land combinaties gebaseerd op de top 5 beleveringen per combinatie.


Dus zoiets


Select herkomstland, bestemmingsland, (gemiddelde van top 5 van de unieke combinatie),


Bedankt

[Verwijderd]

Nog eens een poging van mezelf:

select prodland, bestland, (select avg(hoev) from tbl where hoev in (select top 5 hoev from tbl))
from tbl
group by prodland, bestland


Weet eigenlijk niet of je wel een select als kolom kan doen als je een group by hebt

[D4Skunk]

Heel eenvoudig : werk met een tussentijdse query

code:

q1 = 
select top5 sum(hoev) as sumhoev 
from tbl 
order by sum hoev

q2 = 
select avg(sumhoev) 
from q1

[Verwijderd]

De query geeft geen errors, maar nu zijn de gemiddelden van alle hoeveelheden gelijk, en dat is niet het geval...

Dus hij geeft de juiste tabel terug, maar met allemaal dezelfde gemiddelden


...
Bedankt

[Verwijderd]

EUREKA! heb zelf maar ff een tabel in acces gemaakt. Dit is wat mijn resultaat is

code:

SELECT tbl.prod, tbl.dest, (
    select avg(hoev)
    from tbl t  
    where t.prod=tbl.prod 
    and t.dest = tbl.dest
    and t.hoev in(
        select top 5 hoev
        from tbl b
        where b.dest = tbl.dest
        and b.prod = tbl.prod
        )
    ) AS hoevkg
FROM tbl
GROUP BY tbl.prod, tbl.dest;

Heb de tabel wel niet volledig opgevuld. Alleen met de gegevens uit je startpost

[ Voor 10% gewijzigd door Verwijderd op 31-05-2005 11:22 ]

[D4Skunk]

ow ik mot de vraag wat beter lezen...

[Verwijderd]

Verwijderd schreef op dinsdag 31 mei 2005 @ 11:21:
EUREKA! heb zelf maar ff een tabel in acces gemaakt. Dit is wat mijn resultaat is

code:

SELECT tbl.prod, tbl.dest, (
    select avg(hoev)
    from tbl t  
    where t.prod=tbl.prod 
    and t.dest = tbl.dest
    and t.hoev in(
        select top 5 hoev
        from tbl b
        where b.dest = tbl.dest
        and b.prod = tbl.prod
        )
    ) AS hoevkg
FROM tbl
GROUP BY tbl.prod, tbl.dest;

Heb de tabel wel niet volledig opgevuld. Alleen met de gegevens uit je startpost

MERCI, ik krijg de volgende fout melding : You tried to execute a query that does not include the specified expression "Country of plant" as part of an aggregate function

SELECT ORDERS.[Country of Plant], ORDERS.DestC, (
SELECT Avg(ORDERS.[Delivery quantity])
FROM ORDERS t
WHERE t.[Country of Plant]= ORDERS.[Country of Plant]
AND t.DestC=ORDERS.DestC
AND t.[Delivery quantity] IN (
SELECT Top 5 [Delivery quantity]
FROM ORDERS b
WHERE b.DestC=ORDERS.DestC
AND b.[Country of Plant]=ORDERS.[Country of Plant]
)
)
AS HoevKG
From ORDERS
GROUP BY ORDERS.[Country of Plant], ORDERS.DestC;


Ik hoop dat ik hem goed "vertaald" Heb...

[Verwijderd]

Geen enkel idee waar het aan kan liggen.
Ik gelooof dat acces geen ""-s gebruikt tussen die []? ofwel?

Probeer eens met het volgende:
GROUP BY 1, 2

[farlane]

Het gaat om dat veld in de subquery, die moet ook in de group by staan

[Verwijderd]

Nee want dan zou mijn query ook een foutmelding moeten geven. De query die ik gepost heb hierboven geeft bij mij geen foutmelding. Het kan wel zijn dat het resultaat niet goed is, maar denk dat het moet kloppen.

[Verwijderd]

Toch even klagen. Volgens mij is de query van Yellow Snow niet helemaal optimaal. Probleem is dat ik vind dat er een select teveel in staat (niet echt relevant) en dat de berekening van het gemiddelde niet altijd enkel de top 5 gewichten meeneemt. De berekening gaat er een beetje vanuit dat de gewichten uniek zijn. Komt het 5e gewicht meerdere keren voor, neemt de berekening die ook meerdere malen mee. Het gevolg is in dat geval een te laag gemiddelde.

Ik zou het ongeveer zo aanpakken

SQL:

SELECT gemiddelde.Productieland, gemiddelde.Bestemmingsland, Avg(gemiddelde.Gewicht) AS GemVanGewicht
FROM gewichten AS gemiddelde
WHERE (((gemiddelde.OrderID) In (select top 5 OrderID 
from gewichten as DeHoogste
where DeHoogste.productieland=Gemiddelde.productieland 
 and  DeHoogste.Bestemmingsland=Gemiddelde.Bestemmingsland
ORDER By gewicht desc, OrderID
)))
GROUP BY gemiddelde.Productieland, gemiddelde.Bestemmingsland;

Hierrbij ga ik ervan uit dat de tabel minimaal vier velden bevat. Het productie en bestemmingsland, het gewicht én een Primary Key genaamd Order ID. Ook is de ORDER BY misschien wat vreemd
Het OrderID is daar nodig omdat TOP anders meer dan 5 records retourneert en we min of meer hetzelfde probleem hebben als Yellow Snows query. (Zij het waarschijnlijk in mindere mate aangezien daar de Select Avg ook nog eens teveel records meeneemt)

Even ter aanvulling. Ik heb even Yellow Snows query op de volgende data uitgevoerd.

code:

DE NL 10000
DE NL 12000
DE NL 12000
DE NL 15000
DE NL 18000
DE NL 19000
DE NL 20000

Het gemiddelde van de top 5 is 16800. Yellow Snows query geeft 15200 terug. Toch wel een groot verschil.

[ Voor 20% gewijzigd door Verwijderd op 01-06-2005 12:03 ]

[Verwijderd]

Daar was ik niet zeker van hoe acces dat aanpakte. Enkel de eerste 5 rijen uit die top, of al de waarden van hoeveelheid die in die top 5 voorkomen.

Weer wat bijgeleerd.

[Verwijderd]

Allemaal,

Bedankt! Het werkt!
Ik heb eerst iedere order een tranport ID gegeven op basis van de unieke combinaties. En vervolgens deze query gebruikt om een tabel samen te stellen (table add query). Op basis van deze tabek bereken ik met een andere query de gemiddelden, min, max, etc.

SELECT ORDERS.TransportID, tblTransports.[Country of Plant], ORDERS.DestC, ORDERS.[Delivery quantity]
FROM tblTransports INNER JOIN ORDERS ON tblTransports.TransportID = ORDERS.TransportID
WHERE (((ORDERS.[Delivery quantity]) In (select top 5 [delivery quantity] from orders where [transportid]=[tbltransports].[transportid] order by [delivery quantity] desc)))
ORDER BY ORDERS.TransportID, tblTransports.[Country of Plant], ORDERS.DestC, ORDERS.[Delivery quantity] DESC;

Bedankt voor alle input!