How are percentages dis[alyed through an access query?

[csk003]

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

 

[cranebill]

SELECT round(count/(select sum(count)
FROM Table1)*100,2) as "Percent" from table1;

 

[csk003]

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

 

[cranebill]

You can use this in the same query:

SELECT id, type, round(count/(select sum(count)
FROM Table1)*100,2) as "Percent" from table1;

 

[PHV]

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

 

[cranebill]

Oh wait... I see what your doing...

try this

SELECT id, type, round(count(*)/(select count(*)
FROM Table1)*100,2) as "Percent" from table1
group by id, type;

 

[cranebill]

Shoot... if your counting remove id from my example.

 

[csk003]

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?