I've tried following a guide at
in order to give me the TOP n records by group, but I cannot seem to get it to work.
I got a database containing categories (RVG), buyers (LNR), and amounts (KP)
What I'm interested in getting is the sum of the amounts (KP) of the top 5 buyers pr category. So far I've got this:
SELECT RAWDATA.RVG, Sum(RAWDATA.KP), RAWDATA.LNR
WHERE RAWDATA.LNR IN
(SELECT TOP 5 LNR
FROM RAWDATA AS Dupe
WHERE Dupe.RVG = RAWDATA.RVG
ORDER BY Sum(Dupe.KP) DESC, Dupe.LNR DESC)
ORDER BY RAWDATA.RVG, Sum(RAWDATA.KP), RAWDATA.LNR;
I'm getting the error that RVG is not part of the aggregate function?
Secondly, is it possible to also do a sub query that returns the total spend for that category?
So that I would get:
RVG, LNR, Sum of KP (top 5), Sum of KP (All)?
Thanks in advance!