I need to count the nulls and sum the values. The column is a decimal data type. For the example below, I would need the row to display 17. The statement below seems to work, except when the values are all null. For example, if all the values below were 'null' it would display 'null' where it should be 7. Thanks in advance.
3, 4, 2, null, 2, null,4
,(sum(sc.numberofprocedures) + count(case when sc.numberofprocedures is null then 1 end)) 'ProcCount'
Maybe?:
,(sum(isnull(sc.numberofprocedures, 1))) 'ProcCount'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 11, 2021 at 7:18 pm
Hi,
As the case statement is the usual go to, you could try the case statement as below.
sum(case when sc.numberofprocedures is null then 1 else sc.numberofprocedures end)
November 20, 2021 at 9:21 am
SUM of values of a field or column of a SQL table, generated using SQL . The same approach can be used with SQL COUNT() function too
November 20, 2021 at 10:46 am
+1 for Scotts suggestion. Personally I prefer the COALESCE() operator over ISNULL. There are too many other places where the NULL word appears in syntax so I find COALESCE() has lower cognitive overhead. You certainly could use a CASE statement inside the SUM but I think it makes the code harder to read and reason about.
April 29, 2022 at 4:05 am
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy