SQL query that multiplies the result of SUM by the number of internal elements

Question

I have a query that lists functions based on the status and availability of components.

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 follows:

 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

 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 has 8, 8 and 7 models, respectively. This means that my query performs a priority calculation for all of them: Impact*models + Visibility*models + Risk*models .

How can I make my SQL query not do this multiplication?

thanks

Answer 1

Based on your comment, and if I don’t understand what you need, I don’t think you need SUM() . I think you just need something common for each model. Using SUM() , you will get the total number of all records, which is not like what you want. This will 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)