Divide count by count(*) in SQL Server

sql sql-server count alias divide
Vincent Ducroquet picture Vincent Ducroquet · Apr 13, 2017 · Viewed 21.7k times · Source

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

Answer

Martin Smith picture Martin Smith · Apr 13, 2017

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)

Related questions

Fastest way to determine if record exists

As the title suggests... I'm trying to figure out the fastest way with the least overhead to determine if a record exists in a table or not. Sample query: SELECT COUNT(*) FROM products WHERE products.id = ?; vs SELECT COUNT(products.…

Read More
Select COUNT(*) of subquery without running it twice

I've got a procedure to return a result set which is limited by page number and some other stuff. As an OUTPUT parameter I need to return a total amount of selected rows according to the parameters except the page …

Read More
How can you use COUNT() in a comparison in a SELECT CASE clause in Sql Server?

Let's say you want do something along the following lines: SELECT CASE WHEN (SELECT COUNT(id) FROM table WHERE column2 = 4) > 0 THEN 1 ELSE 0 END Basically just return 1 when there's one or more rows in the table, 0 otherwise. There has to …

Read More