none
Sum of a column with NULL values RRS feed

  • Question

  • I need a query which will return null while doing sum(fieldA) in sql server 2000 if this fieldA contains NULL values. Please help. URGENT!!!


    Thursday, July 14, 2011 7:24 AM

Answers

  • Like this?
    select fielda, case when exists (select 1 from #tmp where fieldb is null and fielda = a.fielda) then null else sum(fieldb) end, min(fieldb) from #tmp a
    group by fielda
    Thursday, July 14, 2011 8:31 AM

All replies

  • try

    sum(isnull(fieldA,0)

    NULL + value is always null, also in sum()

    • Proposed as answer by Rishabh K Thursday, July 14, 2011 7:31 AM
    • Unproposed as answer by Marble Giraffe Thursday, July 14, 2011 7:33 AM
    • Proposed as answer by stepstobegin Wednesday, April 16, 2014 10:57 AM
    Thursday, July 14, 2011 7:25 AM
  • FIELDB FIELDA

    USERA    1

    USERB    1

    USERC    NULL

    If we write a query select sum(fieldA) from tbl then it will ignore the null value and return 2 as output

    But I want NULL to be output through some query. (Aggregate functions ignore null values)

     

    Thursday, July 14, 2011 7:32 AM
  • Hi,

    try this:

    select SUM(case when col is null then col end) from tablename

    Or

    select sum(col) from tablename

    where col is null


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Thursday, July 14, 2011 7:37 AM
  • Basically my requirement is... if all the values of a column have numbers then sum of them should be returned, but if atleast one record in that column has a null value, then the sum should return NULL. Is this possible?

    Hence... I cannot give "where col is null"

    Thursday, July 14, 2011 7:43 AM
  • Try

    create table #tmp (fielda varchar(10), fieldb int)
    insert into #tmp
    select 'a',1 union all select 'b',2 union all select 'c',NULL

    select * from #tmp

    select case when exists (select 1 from #tmp where fieldb is null) then null else sum(fieldb) end, min(fieldb) from #tmp

    Thursday, July 14, 2011 7:47 AM
  • SELECT SUM([MyField]) FROM [MyTable] WHERE (SELECT COUNT(*) FROM MyTable WHERE IsNull([MyField].-1)=-1)=0
    

    Thursday, July 14, 2011 7:58 AM
  • Thanks Christa...

    But can you help me do the same with a third column for which group by should be applied. This may solve my problem.

    Thursday, July 14, 2011 8:22 AM
  • Like this?
    select fielda, case when exists (select 1 from #tmp where fieldb is null and fielda = a.fielda) then null else sum(fieldb) end, min(fieldb) from #tmp a
    group by fielda
    Thursday, July 14, 2011 8:31 AM
  • At last I found the solution... jus seconds before Christa's latest reply. Pasting my query below:

    SELECT

    O.FAO_CLIENT_ID, O.FAO_CLIENT_NAME, CASE WHEN EXISTS (SELECT 1 FROM #TMP P WHERE O.FAO_CLIENT_ID = P.FAO_CLIENT_ID AND STATUS IS NULL) THEN NULL ELSE SUM(STATUS) END AS 'STATUS' FROM #TMP O

    GROUP BY FAO_CLIENT_ID, FAO_CLIENT_NAME

    Thanks a lot Christa :-)

    Thursday, July 14, 2011 8:35 AM
  • > Basically my requirement is... if all the values of a column have numbers then sum of them should be returned, but if atleast one record in that column has a null value, then the sum should return NULL. Is this possible?

    Here is a soluttion that does not use any subquery like the other seem to do:

    SELECT CASE WHEN SUM(CASE WHEN col IS NULL THEN 1 ELSE 0 END) >= 1
                THEN 1
                ELSE SUM(col)
           EMD
    FROM   tbl

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Gert-Jan Strik Wednesday, December 4, 2013 8:22 PM
    Thursday, July 14, 2011 10:02 AM
  • Thanks

    Wednesday, December 4, 2013 6:34 PM
  • i hv considered column 2 having null value,

    Sum (Columname1+COALESCE(Columname2,0) From Tablename

    Wednesday, April 16, 2014 10:54 AM
  • I had the same requirement, also I have no love for the ANSI warning when summing nulls:

    Warning: Null value is eliminated by an aggregate or other SET operation.

    So I went with this approach which allows arbitrary group by.  You'd really think this should be a lot easier than it is.

    SELECT CASE WHEN MAX(CASE WHEN a.MaxCapacity IS NULL THEN 1 ELSE 0 END) = 1 THEN NULL ELSE 1.0 END * SUM(ISNULL(MaxCapacity, 0))
    FROM
    (
    SELECT 100.0
    UNION ALL SELECT 200
    UNION ALL SELECT 300
    UNION ALL SELECT 400
    UNION ALL SELECT 500
    UNION ALL SELECT 600
    --UNION ALL SELECT NULL
    ) AS a(MaxCapacity)


    Friday, October 7, 2016 8:45 PM