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
gurugantaAsked:
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.

Guy Hengel [angelIII / a3]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)

nmwis70Commented:
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
nmwis70Commented:
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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

gurugantaAuthor 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.
nmwis70Commented:
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

Your issues matter to us.

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

Start your 7-day free trial
gurugantaAuthor Commented:
Hi Nmwis70,

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

Thanks for your time.

 
nmwis70Commented:
What database are you using?
gurugantaAuthor Commented:
Access
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.