Divide count by count(*) in SQL Server

Question

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

So this seemingly "easy" question has collected some very interesting answers and comments... — Giorgos Altanis, Apr 13 '17 at 15:19

score 27 · Accepted Answer · answered Apr 13 '17 at 15:12

27

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)

answered Apr 13 '17 at 15:12

Martin Smith

438,706
87
741
845

2

How interesting! – Giorgos Altanis Apr 13 '17 at 15:15
1

Much simpler, +1 – SqlZim Apr 13 '17 at 15:27
1

Oh man, how did you figure this out!? Why does 1.0 work and not 1? Tricky – unnknown Feb 23 '18 at 15:59
Works with Laravel `\DB::raw()` (orderBy AVG of join), +1 – Mtxz Feb 26 '19 at 13:42
ref the 1.0 trick. dividing int/int gives int. dividing int/numeric, numeric/int or numeric/numeric gives numeric. U may check with select sql_variant_property(1/1.0, 'basetype'). – Sindre Aug 13 '21 at 10:08

score 6 · Answer 2 · edited Jan 02 '18 at 21:11

6

I needed to solve a similar problem with an arbitrary number of features (not just null versus non-null), and I also wanted them converted to percentages. This may be helpful for you or someone else with a similar question:

WITH counts AS 
  (SELECT [feature], 
  COUNT(*) AS cnt
  FROM [your_table]
  GROUP BY [feature])
SELECT [feature], CAST(100 * num AS DOUBLE) / (SELECT SUM(num) FROM counts) 
  AS pct
FROM counts
ORDER BY pct DESC

edited Jan 02 '18 at 21:11

Paul Roub

36,322
27
84
93

answered Jan 02 '18 at 20:49

mr_snuffles

312
2
3

You were right! Any idea how to filter so you only keep percentages > 5% (for example). Thanks! – TheSciGuy Aug 18 '19 at 20:31
Figured it out! `WITH counts AS (SELECT [feature], COUNT(*) AS cnt FROM [your_table] GROUP BY [feature]) SELECT [feature], CAST(100 * num AS DOUBLE) / (SELECT SUM(num) FROM counts) AS pct FROM counts **WHERE pct > 5** ORDER BY pct DESC` – TheSciGuy Aug 18 '19 at 20:34
Hi a bit new to these but where is "num" coming from ? I don't see it called anywhere ? – Aspiring Developer Mar 19 '21 at 23:03

SqlZim · Answer 3 · 2017-04-13T15:20:27.123

2

Unfortunately you can not use an alias like that in Sql Server; you have to repeat the expression. You can (as you have already found and others have posted) use a subquery/cte/join etc to return a column with that alias and use it like that, but that is then the column/expression's name, not an alias.

SELECT Count(*) as total,
count(CASE WHEN t.id is null THEN 1 END)/(Count(*)+.0)  as nb_null,
COUNT(CASE WHEN t.id is not null THEN 1 END) as nb_not_null
from table t

Also, add +.0 to either side of your division equation to avoid integer division (returning 0 instead of 0.dddd for percent).

edited Apr 13 '17 at 15:20

answered Apr 13 '17 at 15:08

SqlZim

37,248
6
41
59

1

Upvoting for the integer division remedy! – Giorgos Altanis Apr 13 '17 at 15:11
The "you can not" makes this a wrong answer though. – Apr 13 '17 at 15:12
1

`COUNT` always returns an integer, regardless of its input. You need `1.0 * COUNT(...)` or `SUM`. – Jeroen Mostert Apr 13 '17 at 15:13
@hvd it is a very good answer, if you read the question carefully :-) – Giorgos Altanis Apr 13 '17 at 15:14
@GiorgosAltanis To the question "Is it possible to divide a field by an alias?" (a quote from the question), the answer "you can not" is incorrect and the OP has shown how it can be done. (The edited answer covers this. Now it's merely a slight disagreement on terminology, but the answer looks okay.) – Apr 13 '17 at 15:17
@JeroenMostert Good catch – SqlZim Apr 13 '17 at 15:17
@Hvd Once you have wrapped the query up as a subquery or cte or whatever, it is returning an expression with a name, so that name is no longer an alias in the scope of the outer query. So I guess it would depend on how strictly you define `alias`. – SqlZim Apr 13 '17 at 15:19
1

But doesn't alias have a well-defined definition? – Giorgos Altanis Apr 13 '17 at 15:21
@GiorgosAltanis https://learn.microsoft.com/en-us/sql/t-sql/queries/select-clause-transact-sql column_ alias Is an alternative name to replace the column name in the query result set. For example, an alias such as Quantity, or Quantity to Date, or Qty can be specified for a column named quantity.....column_alias can be used in an ORDER BY clause. However, it cannot be used in a WHERE, GROUP BY, or HAVING clause. If the query expression is part of a DECLARE CURSOR statement, column_alias cannot be used in the FOR UPDATE clause. – SqlZim Apr 13 '17 at 15:25
@SqlZim this was a rhetoric question, that's why I have been defending your answer from the start of this discussion! – Giorgos Altanis Apr 13 '17 at 15:27
@GiorgosAltanis I know, and I appreciate that :) I just thought I'd post it here anyway since you asked. – SqlZim Apr 13 '17 at 15:27
@GiorgosAltanis No, it doesn't have a well-defined definition. The documentation specifies that in `SELECT 1 AS x`, `x` is an alias, but it doesn't specify whether the first `x` in `SELECT x FROM (SELECT 1 AS x) AS y` is still a use of an alias, or whether it's now a non-alias column name. I agree with SqlZim's comment: it depends on how strictly you define "alias". – Apr 13 '17 at 15:36
@hvd So you suggest to give the definition the benefit of the doubt, because it doesn't state explicitly that this is not an alias? Might be so, but still such interpretations cannot justify the confidence by which you declared the answer wrong... Take care! – Giorgos Altanis Apr 13 '17 at 15:41
@GiorgosAltanis Even if we can quibble over whether "alias" was the right term to use, the actual problem the OP was asking help with was avoiding repetition of the expression. The answer suggested that the repetition was unavoidable, and that's what I thought wrong. The answer now no longer makes that suggestion. I think it's okay if we don't agree on this though. – Apr 13 '17 at 15:54

score 1 · Answer 4 · answered Apr 13 '17 at 15:09

1

Ok i found it myself :

SELECT nb_null/total from(
   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
) as req

answered Apr 13 '17 at 15:09

Vincent Ducroquet

864
4
14
24

1

This would be my approach as well. By the way, dividing nb_null / total will give 0! – Giorgos Altanis Apr 13 '17 at 15:11
That's right. There are a few other ways that could work too, such as `WITH req AS (SELECT ...) SELECT nb_null/total FROM req;`. – Apr 13 '17 at 15:11

score 1 · Answer 5 · answered Apr 13 '17 at 15:17

1

I would write this as:

select Count(*) as total,
       avg(case when t.id is null then 1.0 else 0 end) as nb_null,
       count(t.id) as nb_not_null
from table t;

The definition of COUNT(<col>) is that it counts non-NULL values; you might was well use the built-in function.

However, why a column called id could be NULL is beyond me. It should be declared NOT NULL.

answered Apr 13 '17 at 15:17

Gordon Linoff

1,242,037
58
646
786

`t` could be an outer joined table maybe in the real query? – Martin Smith Apr 13 '17 at 15:18
Nobody can mess with @Gordon ever ever. He is beyond this planet. – Ankit Bajpai Apr 13 '17 at 15:19

Divide count by count(*) in SQL Server

5 Answers5

Linked