CODEWITHSUNDEEP
sqlms-accessms-access-query
user23471091
user23471091

Reputation: 53

Having trouble with MS Access percentile query

I have a MS Access table with a Category field and DollarAmount field.

I'm trying to query the table where it groups the data by Category and then shows the 10th percentile of the DollarAmount for each Category.

I used the below SQL and it works but it's dreadfully slow due to it using a subquery (I'm assuming). I tried separating them but that didn't work, because it purely showed the 10th percentile across the whole data set, and not by category.

Any alternate suggestions?

SELECT 
tblComplaints.Category, 
Max(tblComplaints.[DollarAmount]) AS 10thPercentileCalc

FROM tblComplaints

WHERE (((tblComplaints.[DollarAmount])
In (SELECT TOP 10 PERCENT [DollarAmount]
FROM tblComplaints AS sub
WHERE sub.Category= tblComplaints.Category
ORDER BY [DollarAmount] ASC)))

GROUP BY tblComplaints.Category;

Upvotes: 0

Views: 55

Answers (0)

Related Questions