SUM(columnname) gives me a different result than manually summing columnname in SQL Server. Why?

Question

Why is the SUM(Sold) giving me a different result than manually summing the field?

Here's the query that gives me the right # if I manually sum the Sold field:

SELECT     CustCode, FiscalYear, Sold 
FROM         DBNAME.dbo.SALESDETAIL
WHERE     (FiscalYear = YEAR({ fn NOW() }))
and CustCode = '248'
GROUP BY CustCode, FiscalYear, Sold

But if I try to use the SUM function, I get the wrong amount. Can anyone help me out?

SELECT     CustCode, FiscalYear, SUM(Sold) 
FROM         DBNAME.dbo.SALESDETAIL
WHERE     (FiscalYear = YEAR({ fn NOW() }))
and CustCode = '248'
GROUP BY CustCode, FiscalYear

The goal is that there is just one result returned- CustCode, FiscalYear, and Sum(Sold). Appreciate any pointers!

Answer 1

SELECT CustCode, FiscalYear, Sold 
FROM DBNAME.dbo.SALESDETAIL
WHERE (FiscalYear = YEAR({ fn NOW() }))
  AND CustCode = '248'
GROUP BY CustCode, FiscalYear, Sold;

is the same as

SELECT  DISTINCT CustCode, FiscalYear, Sold 
FROM DBNAME.dbo.SALESDETAIL
WHERE (FiscalYear = YEAR({ fn NOW() }))
  AND CustCode = '248';

basically you get rid off multiple Sold values.

Try summing:

SELECT CustCode, FiscalYear, Sold 
FROM DBNAME.dbo.SALESDETAIL
WHERE (FiscalYear = YEAR({ fn NOW() }))
  AND CustCode = '248';

and compare with SUM(Sold) result.

Answer 2

The first query groups by CustCode, FiscalYear, and Sold. I.e., it returns one row per unique combination of these fields. This means that if you have several entries for the same CustCode and FiscalYear that have the same Sold, you'll only return one of these rows, and lose the duplicates. Therefore, your manual summing is missing values, which the second query does not miss.