I have a table with column named type. I can group and get the counts. Can I get percentages also in the same query?
E.g. Percentage of type A = (50/400)*100
Type Count
A 50
B 200
C 150
I can create a new table with counts and do this but I was wondering if access had a way to calculate counts and percentages in same query
Original table has two fields
id & type
You may try either this:
SELECT A.Type, Count(*) As CountOfType, 100*Count(*)/B.Total As Percentage
FROM yourTable AS A, (SELECT Count(*) AS Total FROM yourTable) AS B
GROUP BY A.Type, B.Total
Or this:
SELECT Type, Count(*) As CountOfType, 100*Count(*)/(SELECT Count(*) FROM yourTable) As Percentage
FROM yourTable
GROUP BY Type, (SELECT Count(*) FROM yourTable)
Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
Thank you for helping me out.
I used something similar in the meanwhile but didn't group by total or SELECT Count(*) FROM yourTable
This is working but I am not sure if I am doing it correctly. Do I need to group by total too?
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.