sum returns 0 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

sum returns 0

Author

Topic

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-10 : 02:39:41

Hi everybody, and thanks in advance for your time and patience, I have simple query to add and order a column by month as follows;

Select month(transdate)as MonthPart, sum(amountcur)as MonthTotal
From temp
Group by month(transdate)
Order by month(transdate)

The results of this query is the 12 months as expected, however, the sum value is zero for each month. The data type for amountcur is numeric(28,12).

Could anyone explain why?
Thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 03:12:05

Add a count to see how manu items there are in each month.

Select month(transdate)as MonthPart, sum(amountcur)as MonthTotal, count(*)
From temp
Group by month(transdate)
Order by month(transdate)

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 03:40:05

Also add MIN and MAX for debugging purposes, just in case there are only two values such as +5 and -5 for all months.

Select month(transdate)as MonthPart, sum(amountcur)as MonthTotal, count(*), min(amountcur), max(amountcur)
From temp
Group by month(transdate)
Order by month(transdate)

E 12°55'05.63"
N 56°04'39.26"

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-10 : 04:27:45

Thanks Peso,
Have the results,

MonthPart,MonthTotal,Records,MinVal,MaxVal
1,0.000000000000,44428,-72720981.050000000000,72720981.050000000000
2,0.000000000000,55095,-119830000.000000000000,119830000.000000000000
3,0.000000000000,61382,-114404756.320000000000,122594360.320000000000
4,0.000000000000,68557,-90500000.000000000000,90500000.000000000000
5,0.000000000000,67067,-68824892.880000000000,69069596.370000000000
6,0.000000000000,88326,-570629661.850000000000,206581123.450000000000
7,0.000000000000,45257,-324383177.960000000000,767603378.630000000000
8,0.000000000000,39900,-47000000.000000000000,47000000.000000000000
9,0.000000000000,36327,-101062197.030000000000,101062197.030000000000
10,0.000000000000,70106,-55101656.850000000000,62665459.130000000000
11,0.000000000000,54026,-72162857.950000000000,73311474.030000000000
12,0.000000000000,43801,-44155646.160000000000,53814653.440000000000

It does appear as though there are negetive and positive values cancelling each other out, but this still does not explain, why the returned values are zero for all months

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 04:31:56

How many of there are zero?

-- Peso 1
Select month(transdate)as MonthPart, sum(amountcur)as MonthTotal, count(*), min(amountcur), max(amountcur),
sum(case when amountcur = 0.0E then 1 else 0 end) AS zeros,
sum(case when amountcur is null then 1 else 0 end) AS nulls,
avg(amountcur) AS averagevalue
From temp
Group by month(transdate)
Order by month(transdate)

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 04:42:48

And what does this query return?

-- Peso 2
SELECT		AmountCur,
		SUM(CASE WHEN theMonth = 01 THEN 1 ELSE 0 END) AS [01],
		SUM(CASE WHEN theMonth = 02 THEN 1 ELSE 0 END) AS [02],
		SUM(CASE WHEN theMonth = 03 THEN 1 ELSE 0 END) AS [03],
		SUM(CASE WHEN theMonth = 04 THEN 1 ELSE 0 END) AS [04],
		SUM(CASE WHEN theMonth = 05 THEN 1 ELSE 0 END) AS [05],
		SUM(CASE WHEN theMonth = 06 THEN 1 ELSE 0 END) AS [06],
		SUM(CASE WHEN theMonth = 07 THEN 1 ELSE 0 END) AS [07],
		SUM(CASE WHEN theMonth = 08 THEN 1 ELSE 0 END) AS [08],
		SUM(CASE WHEN theMonth = 09 THEN 1 ELSE 0 END) AS [09],
		SUM(CASE WHEN theMonth = 10 THEN 1 ELSE 0 END) AS [10],
		SUM(CASE WHEN theMonth = 11 THEN 1 ELSE 0 END) AS [11],
		SUM(CASE WHEN theMonth = 12 THEN 1 ELSE 0 END) AS [12],
		COUNT(*) AS Items
FROM		(
			SELECT	DATEPART(MONTH, TransDate) AS theMonth,
				AmountCur
			FROM	Temp
		) AS s
GROUP BY	AmountCur
ORDER BY	AmountCur

E 12°55'05.63"
N 56°04'39.26"

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-10 : 04:55:02

Here are the results....

MonthPart,MonthTotal,(No column name),(No column name),(No column name),zeros,nulls,averagevalue
1,0.000000000000,44428,-72720981.050000000000,72720981.050000000000,0,0,0.000000000000
2,0.000000000000,55095,-119830000.000000000000,119830000.000000000000,0,0,0.000000000000
3,0.000000000000,61382,-114404756.320000000000,122594360.320000000000,0,0,0.000000000000
4,0.000000000000,68557,-90500000.000000000000,90500000.000000000000,0,0,0.000000000000
5,0.000000000000,67067,-68824892.880000000000,69069596.370000000000,0,0,0.000000000000
6,0.000000000000,88326,-570629661.850000000000,206581123.450000000000,0,0,0.000000000000
7,0.000000000000,45257,-324383177.960000000000,767603378.630000000000,0,0,0.000000000000
8,0.000000000000,39900,-47000000.000000000000,47000000.000000000000,0,0,0.000000000000
9,0.000000000000,36327,-101062197.030000000000,101062197.030000000000,0,0,0.000000000000
10,0.000000000000,70106,-55101656.850000000000,62665459.130000000000,0,0,0.000000000000
11,0.000000000000,54026,-72162857.950000000000,73311474.030000000000,0,0,0.000000000000
12,0.000000000000,43801,-44155646.160000000000,53814653.440000000000,0,0,0.000000000000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 05:00:36

The bad thing here is the AVERAGE value. It seems to be 0.0, which means the sum of postive values is equal to the sum of negative values.

Is there an import gone wrong here?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 05:03:50

As you can see by this example, SQL Server is capable of summing DECIMAL(28, 12)

DECLARE	@Sample TABLE
	(
		TransDate DATETIME,
		AmountCur NUMERIC(28, 12)
	)

INSERT	@Sample
SELECT	GETDATE(), 10 UNION ALL
SELECT	GETDATE(), 3 UNION ALL
SELECT	GETDATE() - 60, 3 UNION ALL
SELECT	GETDATE() - 60, 15

-- Peso 1
SELECT		MONTH(TransDate) AS MonthPart,
		SUM(AmountCur) AS MonthTotal,
		COUNT(*) AS Items,
		MIN(AmountCur) AS Minimum,
		MAX(AmountCur) AS Maximum,
		SUM(CASE WHEN AmountCur = 0.0E THEN 1 ELSE 0 END) AS Zeros,
		SUM(CASE WHEN AmountCur IS NULL THEN 1 ELSE 0 END) AS Nulls,
		AVG(AmountCur) AS AverageValue
FROM		@Sample
GROUP BY	MONTH(TransDate)
ORDER BY	MONTH(TransDate)

-- Peso 2
SELECT		AmountCur,
		SUM(CASE WHEN theMonth = 01 THEN 1 ELSE 0 END) AS [01],
		SUM(CASE WHEN theMonth = 02 THEN 1 ELSE 0 END) AS [02],
		SUM(CASE WHEN theMonth = 03 THEN 1 ELSE 0 END) AS [03],
		SUM(CASE WHEN theMonth = 04 THEN 1 ELSE 0 END) AS [04],
		SUM(CASE WHEN theMonth = 05 THEN 1 ELSE 0 END) AS [05],
		SUM(CASE WHEN theMonth = 06 THEN 1 ELSE 0 END) AS [06],
		SUM(CASE WHEN theMonth = 07 THEN 1 ELSE 0 END) AS [07],
		SUM(CASE WHEN theMonth = 08 THEN 1 ELSE 0 END) AS [08],
		SUM(CASE WHEN theMonth = 09 THEN 1 ELSE 0 END) AS [09],
		SUM(CASE WHEN theMonth = 10 THEN 1 ELSE 0 END) AS [10],
		SUM(CASE WHEN theMonth = 11 THEN 1 ELSE 0 END) AS [11],
		SUM(CASE WHEN theMonth = 12 THEN 1 ELSE 0 END) AS [12],
		COUNT(*) AS Items
FROM		(
			SELECT	DATEPART(MONTH, TransDate) AS theMonth,
				AmountCur
			FROM	@Sample
		) AS s
GROUP BY	AmountCur
ORDER BY	AmountCur

E 12°55'05.63"
N 56°04'39.26"

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-10 : 05:22:34

Returns a few 148678 records, here is a smaple....
AmountCur,01,02,03,04,05,06,07,08,09,10,11,12,Items
-570629661.850000000000,0,0,0,0,0,1,0,0,0,0,0,0,1
-478719344.230000000000,0,0,0,0,0,1,0,0,0,0,0,0,1
-389564879.640000000000,0,0,0,0,0,1,0,0,0,0,0,0,1
-324383177.960000000000,0,0,0,0,0,0,1,0,0,0,0,0,1
-209620619.240000000000,0,0,0,0,0,0,1,0,0,0,0,0,1
-206581123.450000000000,0,0,0,0,0,1,2,0,0,0,0,0,3
-205071947.170000000000,0,0,0,0,0,1,0,0,0,0,0,0,1
-200902082.810000000000,0,0,0,0,0,1,0,0,0,0,0,0,1
-186012616.120000000000,0,0,0,0,0,0,1,0,0,0,0,0,1
-167597382.350000000000,0,0,0,0,0,1,0,0,0,0,0,0,1
-162377115.830000000000,0,0,0,0,0,0,1,0,0,0,0,0,1
-160936644.590000000000,0,0,0,0,0,0,1,0,0,0,0,0,1
-145800474.850000000000,0,0,0,0,0,1,0,0,0,0,0,0,1
-142141137.770000000000,0,0,0,0,0,1,0,0,0,0,0,0,1
-141133651.200000000000,0,0,0,0,0,4,0,0,0,0,0,0,4
-136642169.530000000000,0,0,0,0,0,1,0,0,0,0,0,0,1
-136607398.990000000000,0,0,0,0,0,1,0,0,0,0,0,0,1
-135903772.010000000000,0,0,0,0,0,1,0,0,0,0,0,0,1
-135562317.790000000000,0,0,0,0,0,0,1,0,0,0,0,0,1
-131695367.810000000000,0,0,0,0,0,0,1,0,0,0,0,0,1
-131417066.850000000000,0,0,0,0,0,0,1,0,0,0,0,0,1
-131282771.190000000000,0,0,0,0,0,1,0,0,0,0,0,0,1
-120504992.100000000000,0,0,0,0,0,1,0,0,0,0,0,0,1
-119830000.000000000000,0,1,0,0,0,0,0,0,0,0,0,0,1
-114404756.320000000000,0,0,1,0,0,0,0,0,0,0,0,0,1
-109323510.560000000000,0,0,0,0,0,1,3,0,0,0,0,0,4
-108784981.110000000000,0,1,0,0,0,0,0,0,0,0,0,0,1
-107804335.040000000000,0,0,0,0,0,2,0,0,0,0,0,0,2
-107343302.400000000000,0,0,0,0,0,0,1,0,0,0,0,0,1
-105358046.840000000000,0,0,0,0,0,1,1,0,0,0,0,0,2
-104771740.730000000000,0,0,0,0,0,1,0,0,0,0,0,0,1
-103694880.560000000000,0,0,0,0,0,1,0,0,0,0,0,0,1
-101062197.030000000000,0,0,0,0,0,0,0,0,2,0,0,0,2
-100993520.470000000000,0,0,0,0,0,0,1,0,2,0,0,0,3
-100037025.980000000000,0,0,1,0,0,0,0,0,0,0,0,0,1
-97885696.520000000000,0,1,0,0,0,0,0,0,0,0,0,0,1
-94589324.910000000000,0,0,0,0,0,1,0,0,0,0,0,0,1
-93096627.770000000000,0,0,0,0,0,0,1,0,0,0,0,0,1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 05:40:19

And there is no record with AmountCur equal to Zero?

E 12°55'05.63"
N 56°04'39.26"

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-10 : 06:23:37

I have checked for zero's as well as run tht isnull command, but there is nothing....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 06:41:32

Post table create script for Temp table.

E 12°55'05.63"
N 56°04'39.26"

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-12 : 01:32:45

I am busy exporting to Excel, I will run monthly totals against it then....

Durbslaw
Starting Member

43 Posts

Posted - 2009-06-18 : 09:29:31

quote:
Originally posted by Peso

And there is no record with AmountCur equal to Zero?

E 12°55'05.63"
N 56°04'39.26"

Hi Peso,
Thanks for your help, I exported to excel ran the sum totals against the month, and came up with actual total, so I went back to the db, copied the columns into a temp table, changed the datatype from numeric to int and ran the sum against it, in this case the the totals were fine, I re-converted to numeric and ran the sum again and this time the totals turned out fine....

Why? and how do I get production to come off flawlessly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources