# AVG, COUNT AND PERCENT

Hello Access Experts,
I need to get a  AVG,  COUNT AND PERCENT calculation in one query.

I have a table like the one below

ID      STATUS      DAYS FOR COMPLETION
1      Won      2.00
2      Lost      3
3      Won      6.00
4      Draw      4
5      Won      6.00
6      Lost      7
7      Won      7.80
8      Draw      9
9      Won      9.60
10      Lost      11

My solution would look something like this. I am grouping by STATUS
STATUS      Avg Days Per Status      Count of Status    Percent of Each Status
Won             6.73                          5                                50.00%
Lost             7                          3                               30.00%
Draw            6.5                          2                                20.00%

Percent of Each Status was calculated by dividing Count of  Status by Total Count

I can get the AVG and COUNT on one query but I cannot figure out
how to calculate the PERCENT of each status on the same query.

Any suggestions are welcome.

Thanks,
GuruGanta
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Billing EngineerCommented:
Actually, you need the total to be computed as subquery (or better as preliminate query that populates a variable)

Use MS Access you can compute a column like that:

select field1, field2, (select sum(field2) from yourtable where ...) as field3
from yourtable where ...

and of course you can also compute with such a subselect (however query syntax will be difficult to read)

Commented:
SELECT
Status,
(SUM([DAYS FOR COMPLETION]) / COUNT([DAYS FOR COMPLETION])) AS [Average Days Per Status],
COUNT([DAYS FOR COMPLETION]) AS [Count Of Status],
((COUNT(Status) / COUNT(*)) * 100) AS [Percent Of Each Status]
FROM TableName
GROUP BY Status
Commented:
Actually do this, it's getting rounded inadvertently:

SELECT
Status,
(SUM([DAYS FOR COMPLETION]) / COUNT([DAYS FOR COMPLETION])) AS [Average Days Per Status],
COUNT([DAYS FOR COMPLETION]) AS [Count Of Status],
(CONVERT(float,COUNT(*))/CONVERT(float,(SELECT COUNT(*) FROM  TableName)) * 100) AS [Percent Of Each Status]
FROM TableName
GROUP BY Status
Author Commented:
Hi,
Thanks for the suggestions angelIII and nmwis70 .

Nmwis70 , when I use your code, I get Percent Of Each Status =100.
I am trying to find out what the percent of each STATUS group (WON LOST DRAW)is with regards to the total # of Status's.
This is the SQL I have so far: How would I add a calculation for the PERCENT in this.

SELECT Status, Avg(Table1.DAYS FOR COMPLETION) AS [Avg Days], Count(Table1.Status) AS CountOfStatus
FROM Table1
GROUP BY  Status

Thanks.
Commented:
If you used what I posted the second time you should get the appropriate percentages. Try this below which is getting only the percentage

SELECT Status,  (CONVERT(float,COUNT(*))/CONVERT(float,(SELECT COUNT(*) FROM  Table1)) * 100) AS [Avg Days], Count(Table1.Status) AS CountOfStatus
FROM Table1
GROUP BY  Status

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Hi Nmwis70,

Access is not recognizing the function 'CONVERT'.
Any substitutes for that function?