SQL - Dividing the Count of 2 records

Question

User-1917250117 posted

Hi there,

I have a problem that I cannot seem to resolve. We are using an access database to store data from a Quality Assurance Tool. I need to calculate an average on a specific column in the table, however the SQL statement needs to count the number of records that contain "0.1" and divide it by the total number of records in the table. This data is then displayed in an ASP Page.

The field name is 1.

Here is the SQL:

StrSQL =  "(Select Count([1]) FROM cservice Where [1]=0.1) / (Select Count([1]) FROM cservice) As questaverage "

Help would be much appriciated!

 

:)

 

Answer 1

User-1199946673 posted

You need to use a sub query (In bold)!

SELECT Count(c1.[1]) / (SELECT COUNT(c2.[1]) FROM cservice c2) AS questaverage FROM cservice c1 WHERE c1.[1]=0.1 

Answer 2

User-818134166 posted

Try this:

Select tot1/tot2  
from
(Select Count([1]) as tot1 FROM cservice Where [1]=0.1) as [table1],
(Select Count([1]) as tot2 FROM cservice) as [table2] 

--dmnida

*Mark as Answered if it helped

Answer 3

User-1917250117 posted

Thanks to both of you. Both solutions worked.