CODEWITHSUNDEEP
sqlsql-servert-sql
vxd128
vxd128

Reputation: 71

Trouble with SUM not adding while filtering criteria

I'm trying to use SUM to add 3 recordsets worth of data for each individual part based on one field, but it won't SUM, it will just show the results of each record individually.

I've tried modifying the query to limit only to one table with no luck. I've tried using the criteria in both WHERE and HAVING with no changes. If I remove the criteria, then it will do the SUM correctly. 

SELECT dbo.tbl_Parts.Number, 
Sum(dbo.tbl_Msmt_Data.Result) AS Result, 
dbo.tbl_Parts.Parts_ID

FROM dbo.tbl_Msmt_Data INNER JOIN dbo.tbl_Parts ON dbo.tbl_Msmt_Data.Part = dbo.tbl_Parts.Parts_ID

WHERE ((dbo.tbl_Msmt_Data.Time_Completed)>GetDate()-60)

GROUP BY dbo.tbl_Parts.Number, dbo.tbl_Parts.Mold,dbo.tbl_Parts.Parts_ID, dbo.tbl_Msmt_Data.Msmt_Spec

HAVING (((dbo.tbl_Msmt_Data.Msmt_Spec)=4000 Or (dbo.tbl_Msmt_Data.Msmt_Spec)=4001 Or (dbo.tbl_Msmt_Data.Msmt_Spec)=4514))

ORDER BY dbo.tbl_Parts.Number;



What I want the output to read is: 
Number, Result 
1723      590
1724      574

But what I'm getting is: 
Number, Result 
1723      120
1723      470
1724      112
1724        0
1724      462

The same patterns occur for the other part numbers in the same fashion.

Upvotes: 0

Views: 63

Answers (2)

fiveobjects
fiveobjects

Reputation: 4299

Group by only dbo.tbl_Parts.Number instead of: 

GROUP BY dbo.tbl_Parts.Number, dbo.tbl_Parts.Mold, 
dbo.tbl_Parts.Parts_ID, dbo.tbl_Msmt_Data.Msmt_Spec

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33581

You are getting more rows because you have extra columns in the select list and you are grouping by too many columns. Also, you really should learn how to use aliases. It turns that wall of text into a pretty short and easy to decipher query. I also used dateadd to calculate days instead of shorthand. You might be careful there because it will not be accurate if you want the date.

With no sample data to work with I am guessing but this should be really close.

SELECT p.Number, 
    Sum(d.Result) AS Result 
FROM dbo.tbl_Msmt_Data d
INNER JOIN dbo.tbl_Parts  p ON d.Part = p.Parts_ID
WHERE d.Time_Completed > dateadd(day, -60, GetDate())
GROUP BY p.Number
HAVING d.Msmt_Spec in (4000, 4001, 4514)
ORDER BY p.Number;

Upvotes: 2

Related Questions