Handling Null values when finding a Sum of Columns?

Question

0.00/5 (No votes)

See more:

Hi all!
I have a table with (StudentID,Physics,Chemistry,Total,Average)
I want to pull values of Physics,Chemistry and calculate Total and then Average for a specific StudentID. With the following code:

C#

SELECT SUM (IIF(ISNULL([Physics]),0,[Physics])) As Phy, SUM (IIF(ISNULL([Chemistry]),0,[Chemistry])) As Chem, SUM([Physics])+Sum([Chemistry]) as Total FROM tblForm1 WHERE StudentID = '" & Trim(Me.txtSearch.Text) & "'"

I get the following:
If all the values exists in the table, everything is fine, it fetches the values and calculates the sum. But if one or both values are null (not filled) they are replaced with '0' but it does not do the Sum (calculate the total).

Can someone please show me the way through this? I want it to calculate the sum even if one or both values are null (the total will then be '0')

I will appreciate any inputs please.(I use an Access .mdb database)

Posted 14-Nov-12 2:22am

savedlema

Updated 14-Nov-12 2:59am

Keith Barrow

v5

Add a Solution

4 solutions

Solution 4

Thank you all. I have solved the problem.
Many blessings to you.

Posted 16-Nov-12 10:18am

savedlema

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

VedPrakash(Developer) · Accepted Answer · 2012-11-14T03:23:00

Solution 2

Hi,

You are doing so much its simple.

SQL

select	ISNULL(Physics,0) as Physics,
        ISNULL(Chemistry,0) as Chemistry,
        SUM(ISNULL(Physics,0)+ISNULL(Chemistry,0)) as Total,
        (SUM(ISNULL(Physics,0)+ISNULL(Physics,0))/2) as [Avg]
FROM	tblForm1 
WHERE	StudentID = '" & Trim(Me.txtSearch.Text) & "'"

Hope, It will help you.
Thanks

Posted 14-Nov-12 3:23am

VedPrakash(Developer)

Updated 14-Nov-12 3:28am

v2

Comments

savedlema 16-Nov-12 5:28am

Thanks Ved, I have a question please;
I want to INSERT the result of this query (Total) into a field called "TotalMarks" found in the same table. How to I do that?
I tried INSERT INTO TotalMarks SELECT...(previous solution query) but did not work.
I also tried INSET INTO tblForm1.TotalMarks but never worked.

Any idea please?

Thanks a lot so far.

Jacques Loubser · Accepted Answer · 2012-11-14T03:24:00

Hi
Try This

Dim con As New OleDb.OleDbConnection
Dim dbProvider As String
Dim dbSource As String
Dim dbTableName As String
Dim sql1 As String
Dim MaxRows As Integer

dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = "
'Define these two variables
dbSource = "C:/Marks.mdb"
dbTableName = "Science_Marks"

con.ConnectionString = dbProvider & dbSource

Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
sql1="SELECT * FROM " & dbTableName


con.Open()
da = New OleDb.OleDbDataAdapter( sql, con )
da.Fill(ds, "FancyName")
MaxRows = ds.Tables("FancyName").Rows.Count
con.Close()

For i As Integer = 0 To MaxRows - 1
	Dim physics As Double = 0
	Dim chemistry As Double = 0
	Try
physics = ds.Tables("FancyName").Rows(i).Item(1)
	Catch exp As Exception
	End Try
	Try
chemistry = ds.Tables("FancyName").Rows(i).Item(2)
	Catch exp2 As Exception
	End Try
	Dim total As Double = physics + chemistry
	Dim average As Double = total / 2
'Do Whatever with the total and average
Next

This will stop the sql injection problem.

Jacques

Rob Branaghan · Accepted Answer · 2012-11-14T03:13:00

Try this, I am not sure if this will work in Access, as I am far more used to MSSQL2005 and above.

SQL

SELECT
   SUM(ISNULL([Physics],0)) AS Phy,
   SUM(ISNULL([Chemistry],0)) AS Chem,
   SUM(ISNULL([Physics],0)) + SUM (ISNULL([Chemistry],0)) AS Total
FROM tblForm1
WHERE RTRIM(StudentID) = 'STUDENT1'
GROUP BY [Physics]
         ,[Chemistry]

Just paste that into your SQL and then replace 'STUDENT1' with your '" & Trim(Me.txtSearch.Text) & "'" but.... I would be a bit concerned about using it as Dynamic SQL from the VB, as the user could enter SQL Injection to cause some trouble!

Try and use Stored Procedures with Parameters... As I am lead to believe this is safer....

Handling Null values when finding a Sum of Columns?

4 solutions

Solution 2

Solution 3

Solution 1

Solution 4

Add your solution here

Preview 0