查找列总和时是否处理Null值? [英] Handling Null values when finding a Sum of Columns?

问题描述

大家好！
我有一张表格，上面有(学生ID，物理，化学，总计，平均值)
我想获取Physics，Chemistry的值，并为特定的StudentID计算总计"，然后计算平均值".使用以下代码:

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:

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) & "'" 

我得到以下信息:
如果表中存在所有值，则一切都很好，它将获取值并计算总和.但是如果一个或两个值都为空(未填充)，则将它们替换为"0"，但不会进行求和(计算总数).

有人可以告诉我如何进行此操作吗?我希望它即使一个或两个值都为空(总和将为"0")来计算总和

我将不胜感激.(我使用Access .mdb数据库)

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)

推荐答案

您正在做的如此简单.

Hi,

You are doing so much its simple.

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

嗨
试试这个

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

这将停止sql注入问题.

雅克(Jacques)

This will stop the sql injection problem.

Jacques

尝试一下，我不确定这是否可以在Access中使用，因为我已经习惯于MSSQL2005及更高版本.

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

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]

只需将其粘贴到您的SQL中，然后用您的''& Trim(Me.txtSearch.Text)&"''替换" STUDENT1，但是....我会有点担心将它用作来自VB的动态SQL，因为用户可能会输入SQL注入而导致某些麻烦！

尝试使用带参数的存储过程...由于我被认为这是更安全的....

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....

这篇关于查找列总和时是否处理Null值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！