Divide Count by Account (*) in SQL Server
Here is my request:
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 split a field into 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
This doesn't work for me in SQL Server, I would like to know if there is a way to do this? Thanks to
source to share
I needed to solve a similar problem with an arbitrary number of functions (not just zero or non-zero), and I also wanted them to be converted to percentages. This might 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
source to share
Unfortunately, you cannot use alias
both on a Sql server; you have to repeat the expression. You can (as you've 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 it's the column / expression 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
on either side of your division equation to avoid integer division (return 0 instead of 0.dddd for percentages).
source to share
I would write it like:
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 COUNT(<col>)
is that it counts non-NULL values; you could use the built-in function well.
However why the named column id
might be NULL
outside of me. It must be announced NOT NULL
.
source to share