Sum(Case When) Question

Question

I'm trying to get the syntax correct on my query but I'm having issues.

Currently my query will show the Household Total Value (HHValue) and then the NationalValue on one row, then the second row shows the HHValue again and the DirectValue. It's splitting the results between two rows and I want it all on one row.

However, when I try to change my CASE statement, I am getting errors. I think I'm just confusing myself but I need one row for each household with HHValue, NationalValue and DirectValue.

This is what I have in my query so far:

SELECT Household.Name
     , FinancialPlanner.LastName
     , PlanFirmSpecCode.SpecialCode
     , SUM(Asset.Value) AS HHValue
     , SUM(CASE WHEN financialinst.name = 'National Financial Services' 
                THEN Value ELSE 0 
           END) AS NationalValue
     , SUM(CASE WHEN financialinst.name <> 'National Financial Services' 
                THEN Value ELSE 0 
           END) AS DirectValue
FROM PlanFirmSpecCode 
JOIN FinPlanSpecCode 
    ON PlanFirmSpecCode.PlanFirmSpecCodeID 
     = inPlanSpecCode.PlanFirmSpecCodeID 
RIGHT JOIN Household 
      JOIN Account 
          ON Household.HouseholdID = Account.HouseholdID 
      JOIN Asset 
          ON Account.AccountID = Asset.AccountID 
      JOIN FinancialPlanner 
          ON Household.FinancialPlannerID 
           = FinancialPlanner.FinancialPlannerID
      LEFT 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
       , PlanFirmSpecCode.SpecialCode
       , CASE WHEN FinancialInst.Name = 'National Financial Services' 
              THEN 'NFS' ELSE 'Direct Business' 
         END
Order by household.Name

Answer 1

I think you do not need the last column in your GROUP BY clause:

SELECT     Household.Name, FinancialPlanner.LastName, PlanFirmSpecCode.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, PlanFirmSpecCode.SpecialCode
Order by   household.Name