SQL query multiplying SUM result by amount of inner elements

Question

I have a query that lists features, based on components status and availability ``` SELECT Brand.Id , Brand.Name , Brand.Impact , Brand.Visibility , Brand.Risk , SUM(Brand.Impact + Brand.Visibility + Brand.Risk) AS Priority FROM Brand LEFT OUTER JOIN Type ON Brand.Id = Type.FeatureId LEFT OUTER JOIN Model ON Type.Id = Model.SubFeatureId LEFT OUTER JOIN TestingStatus ON Model.StatusId = TestingStatus.Id WHERE (Model.IsDeleted = 'False') AND (Brand.IsDeleted = 'False') AND (Type.IsDeleted = 'False') AND (@TeamId = 0 OR Model.TeamId = @TeamId) GROUP BY Brand.YearId, Brand.Id, Brand.Name, Brand.Impact, Brand.Visibility, Brand.Risk HAVING (Brand.YearId = @YearId) ``` My results are as follow: ``` ID Name Impact Visibility Risk Priority 403 Chevy 1 2 3 48 404 Nissan 1 1 1 24 405 Toyota 3 2 1 42 ``` Instead of ``` ID Name Impact Visibility Risk Priority 403 Chevy 1 2 3 6 404 Nissan 1 1 1 3 405 Toyota 3 2 1 6 ``` Each of these brands have respectively 8, 8 and 7 models. Which means that my qu…

Accepted Answer

Based on your comment and unless I am not understanding what you need, I don't think you need the `SUM()`. I think you just want the total for each model. Using the `SUM()` you are going to get the total for all records which it doesn't sound like you want. This would also remove your `GROUP BY` and `HAVING`

```
SELECT Brand.Id
  , Brand.Name
  , Brand.Impact
  , Brand.Visibility
  , Brand.Risk
  , (Brand.Impact + Brand.Visibility + Brand.Risk) AS Priority

FROM Brand 
LEFT OUTER JOIN Type 
  ON Brand.Id = Type.FeatureId 
LEFT OUTER JOIN Model 
  ON Type.Id = Model.SubFeatureId 
LEFT OUTER JOIN TestingStatus 
  ON Model.StatusId = TestingStatus.Id

WHERE (Model.IsDeleted = 'False') 
  AND (Brand.IsDeleted = 'False') 
  AND (Type.IsDeleted = 'False')
  AND (@TeamId = 0 OR Model.TeamId = @TeamId)
  AND (Brand.YearId = @YearId)
```

SQL query multiplying SUM result by amount of inner elements

Solution

Problem