CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
0nir
0nir

Reputation: 1355

Sum of amount returning 0 instead of actual sum

I have the below query wherein the sum(Amount) field is returning me 0 instead of the actual sum value.

Data type of the amount field is decimal(15,2)

Query:

SELECT Period
        ,count(DISTINCT Trans_Date) as [Date_count]
        ,count(*) as Row_No
        ,sum([Amount]) as [Amount_Value]
        ,count(*)-count([Amount]) as [Amount_null]
        FROM table1 
    GROUP BY Period
    ORDER BY Period

Result: 

Period  Date_count  Row_No  Amount_Value    Amount_null

2014/03 31          15486   0.00            0

Would you suggest any possible reason why? Thanks.

Table Schema :

CREATE TABLE table1(
    [Period] [varchar](7) NOT NULL,
    [Account] [varchar](12) NULL,
    [Trans_Date] [date] NULL,
    [Amount] [decimal](15, 2) NULL
)

Sample Data :

Period  Account Trans_Date  Amount
2014/03 001234  2014-03-01  4517.45
2014/03 001234  2014-03-01  2120.77
2014/03 001234  2014-03-01  3115.69
2014/03 001245  2014-03-01  1803.78
2014/03 001245  2014-03-01  596.33
2014/03 001245  2014-03-01  null

Upvotes: 1

Views: 1331

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The logical conclusion is that the sum of Amount over your data is, in fact, 0.00.

The data you have as a sample, the correct result is returned:

PERIOD  DATE_COUNT  ROW_NO  AMOUNT_VALUE  AMOUNT_NULL
2014/03 1           6       12154.02      1

Here is a SQL Fiddle.

Just to correct some misconceptions from the comments.

  • NULL values are ignored in SUM() (and AVG() and MIN() and MAX() too)
  • count(*)-count([Amount]) is being used correctly to count the number of NULL values (although I prefer the more explicit formulation of sum(case when Amount is NULL then 1 else 0 end)).

Upvotes: 2

Related Questions