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)
August 18, 2021 at 5:23 am
This was removed by the editor as SPAM
September 2, 2021 at 9:27 am
This was removed by the editor as SPAM
November 17, 2021 at 5:21 am
This was removed by the editor as SPAM
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 20, 2022 at 2:05 am
This was removed by the editor as SPAM
April 20, 2022 at 3:15 am
This was removed by the editor as SPAM
April 29, 2022 at 4:05 am
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply