Search code examples
sqlsql-servercountaliasdivide

Divide count by count(*) in SQL Server

Here is my query :

SELECT 
    COUNT(*) AS total,
    COUNT(CASE WHEN t.id IS NULL THEN 1 END) AS nb_null,
    COUNT(CASE WHEN t.id IS NOT NULL THEN 1 END) AS nb_not_null
FROM
    table t

Is it possible to divide a field by an alias? :

SELECT 
    COUNT(*) AS total,
    COUNT(CASE WHEN t.id IS NULL THEN 1 END) / total AS nb_null,
    COUNT(CASE WHEN t.id IS NOT NULL THEN 1 END) AS nb_not_null
FROM
    table t

It doesn't work for me in SQL Server, I'd like to know if there is any way to do this? Thanks

Solution

  • Instead of 

    COUNT(CASE WHEN t.id is null THEN 1 END)/Count(*)

    You can use

    AVG(CASE WHEN t.id is null THEN 1.0 ELSE 0 END)