Solved

SQL SUM Function. Sum the column value to get Grand Total. If one of the column having null value then it does not return the SUM value i.e. The GrandTotal.

Posted on 2004-09-16
1
1,771 Views
Last Modified: 2008-03-17
Hi,

           I would like to sum up three column value.

ProjectID      GETotal          GMTotal         GOTotal             GrandTotal
10                  1000              <null>             2000                                           ' I don't get sum value here because of null
12                   8000           1000                  500                     9500                

SELECT     SUM(GETotal + GMTotal + GOTotal) AS GrandTotal, ProjectID, GETotal, GOTotal, GMTotal
FROM         dbo.View_EMTOBudget
GROUP BY ProjectID, GETotal, GOTotal, GMTotal

But i have to get 3000 for the ProjectID 10.

How do i do it if some of the column has null value.

Thank you.
0
Comment
Question by:casstd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 17

Accepted Solution

by:
BillAn1 earned 150 total points
ID: 12072909
use ISNULL - it replaces the value with a value (in this case 0) if it is NULL :

SELECT     SUM( isnull(GETotal,0) + isnull(GMTotal,0) + isnull(GOTotal,0)) AS GrandTotal, ProjectID, GETotal, GOTotal, GMTotal
FROM         dbo.View_EMTOBudget
GROUP BY ProjectID, GETotal, GOTotal, GMTotal
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question