Calculate percents inline in SQL query

SELECT User, COUNT(*) as count FROM Tests WHERE Release = '1.0' GROUP by User;

Above query will return distinct numbers, ho开发者_开发知识库wever, I would like to convert count to percents in relation to total number of records. Total number of records considering WHERE clause.

SELECT R1.user, COUNT(*)/R2.COUNT_ALL AS Expr1
  FROM Releases R1,
       (SELECT COUNT(*) As COUNT_ALL FROM Releases WHERE Release = '1.0') R2
 WHERE R1.Release = '1.0'
 GROUP BY R1.user, R2.COUNT_ALL

Here's another approach that uses a single SELECT:

SELECT 
    Tests.User, 
    Count(IIf([Tests].[Release]='1.0', 1, Null)) / Count(*) AS Percentage
FROM 
    Tests
GROUP BY 
    Tests.User

Unlike the approaches suggested earlier, this one will, for better or worse, return records for users having no records in Tests where Release is "1.0". If you don't want these records, you could add a HAVING clause to eliminate them.

Hope this helps.

Like Brian's answer but in standard SQL instead of MS Access only. (One can also use COUNT instead of SUM with NULL where I have 0.)

SELECT 
    Tests.User, 
    Sum(CASE WHEN Release='1.0' THEN 1 ELSE 0 END) / Count(*) AS Percentage
FROM 
    Tests
GROUP BY 
    Tests.User