Sql-server – SUM several CASE results

casesql serversum

Given:

 CASE 
     WHEN A IS NULL THEN 1 ELSE 0
 END AS [A]
, CASE
     WHEN B IS NULL THEN 1 ELSE 0
 END AS [B]
, CASE
     WHEN C IS NULL THEN 1 ELSE 0 
 END AS [C]

How can I SUM A+B+C being the results of 3 different CASE WHEN

Best Answer

You did not specify your RDBMS (SQL Server, Oracle, etc.), but if it supports Common Table Expressions, you could encapsulate your main select logic in a CTE and select/sum from it. Here is a solution using SQL Server.

Declare @T table (A int, B int, C int)
insert into @T(A,B,C) values(null,2,null)

;WITH _cte
AS (
    SELECT CASE 
            WHEN A IS NULL
                THEN 1
            ELSE 0
            END AS [A]
        ,CASE 
            WHEN B IS NULL
                THEN 1
            ELSE 0
            END AS [B]
        ,CASE 
            WHEN C IS NULL
                THEN 1
            ELSE 0
            END AS [C]
    FROM @T
    )
SELECT A + B + C as Results
FROM _cte

Results
2

As a comment pointed out, you don't need a common table expression. A derived table will also serve the purpose

Declare @T table (A int, B int, C int)
insert into @T(A,B,C) values(null,2,null)
SELECT A + B + C AS Results
FROM (
    SELECT CASE 
            WHEN A IS NULL
                THEN 1
            ELSE 0
            END AS [A]
        ,CASE 
            WHEN B IS NULL
                THEN 1
            ELSE 0
            END AS [B]
        ,CASE 
            WHEN C IS NULL
                THEN 1
            ELSE 0
            END AS [C]
    FROM @T
    ) a

Related Solutions

Sql-server – Case always returns null

There's no ELSE in your first CASE, which means if none of your WHEN expressions are matched then it returns NULL.

My guess is there's an issue in your logic below that causes date1 and date2 to always be equal, which would miss both your WHENs in the first CASE and return NULL.

Sql-server – Using Case in SQL

Whatever is in the SELECT that is not an aggregate function or literal value needs to be in the GROUP BY statement.
Using the ELSE 0 in the [DirectValue] CASE statement, just like it was already in the [NationalValue] CASE statement, should do the trick.

Try this:

SELECT     Household.Name,
           FinancialPlanner.LastName,
           COALESCE(PlanFirmSpecCode.SpecialCode, FinancialPlanner.LastName) AS [SpecialCode],
           SUM(Asset.Value) AS [HHValue],
           SUM(CASE WHEN FinancialInst.Name = 'National Financial Services' THEN Asset.Value
                    ELSE 0 END) AS [NationalValue],
           SUM(CASE WHEN FinancialInst.Name <> 'National Financial Services' THEN Asset.Value
                    ELSE 0 END) AS [DirectValue]
FROM       PlanFirmSpecCode
INNER JOIN FinPlanSpecCode
        ON PlanFirmSpecCode.PlanFirmSpecCodeID = FinPlanSpecCode.PlanFirmSpecCodeID
RIGHT OUTER JOIN Household
                INNER JOIN Account
                        ON Household.HouseholdID = Account.HouseholdID
                INNER JOIN Asset
                        ON Account.AccountID = Asset.AccountID
                INNER JOIN FinancialPlanner
                        ON Household.FinancialPlannerID = FinancialPlanner.FinancialPlannerID
                LEFT OUTER JOIN FinancialInst
                        ON Account.FinancialInstID = FinancialInst.FinancialInstID
            ON FinPlanSpecCode.FinancialPlannerID = FinancialPlanner.FinancialPlannerID
WHERE      (Account.Closed <> 'Y')
AND        (Asset.Value > '0')
AND        (Account.Managed <> 'N')
GROUP BY   Household.Name,
           FinancialPlanner.LastName,
           COALESCE(PlanFirmSpecCode.SpecialCode, FinancialPlanner.LastName)
ORDER BY   Household.Name;

Best Answer

Related Solutions

Sql-server – Case always returns null

Sql-server – Using Case in SQL

Related Question