• Status: Solved
• Priority: Medium
• Security: Public
• Views: 707

# Calculation of Average in SQL Statement

Hello,
I would appreciate help regarding this:
I have bellow VBA code which I use to select and export data from MS Access database to MS Excell worksheet. It works very fine. But I have problem because I need to set my bellow strSQL statement in way to have also AVERAGE calculated on D_1,D_2 and D_3 values within the same. It would (according to my understanding)look something like this:
strSQL = " SELECT ID, NAME, PP_NO, D_1, D_2, D_3, AVG(D_1,D_2,D_3) from TBL_1 WHERE DATE = " & Me.Combobox.Value& " "

Also I find As huge problem for me(that also must be incorporated in my VBA) is the fact that Average must not take into consideration D_1,D_2 or D_3 if some of them is Null. It means if D_1 = 4, D_2 = 5 and D_3 = 0 then average = (D_1+D_2)/2 because D_3 is Null. Otherwise, if for instance D_3 = 10 then average is (D_1+D_2+D_3)/3
This Average is calculated on level of each ID which are a number of aproximate 100 positions within the table and each one is unique.
Thank you very much.
Kind regards
``````Private Sub Start_Command_Click()
Dim col As Integer
Dim row As Integer

stDB = "MY path to database\db2.mdb"

& "Data Source=" & stDB & ";"

strSQL = " SELECT ID, NAME, PP_NO, D_1, D_2, D_3 from TBL_1 WHERE DATE = " & Me.Combobox.Value& " "

rst.Open strSQL, cnn

col = 0

row = 6
Do While Not rst.EOF
row = row + 1
col = 0

Do While col < rst.Fields.Count
Cells(row, col + 1) = rst.Fields(col).Value
col = col + 1
Loop

rst.MoveNext
Loop

Set rst = Nothing: Set cnn = Nothing
End Sub
``````
0
garfild_1
• 2
1 Solution

Commented:
Hi garfild_1

I understand that you will paste D_1 , D_2 and D_3 lets say in column C,D,E resp. in the excel sheet, so why  not calculate the average in the excel sheet with a formula =SUM(C1:E1)/COUNTIF(C1:E1,">0") for first ID and autofill for the rest

0

Author Commented:
Hello,
Thank you for your reply in such short notice. Your solution is very practical but my boss doesn't want to see in future these 3 columns any more in woksheet (only result which is Average). That is why I need VBA solution. It is easy to exclude the D_1... D_3 fileds from SELECT statement but then I dont know how to calculate Average in my original VBA. Also hiding of columns in Excel Worksheet is not acceptable (as solution) so we are back again on my issue how to adjust VBA in order to calculate Average as described. Thank you very much for your proposition one more time but deffinatelly I need to have this solved within VBA code.
Regards.
0

Commented:
ok how about using the following code then to obtain the average, you can put the avg whereever you want in the output sheet
``````Private Sub Start_Command_Click()
Dim col As Integer
Dim row As Integer
Dim avg as integer,sum as integer,total as integer

stDB = "MY path to database\db2.mdb"

& "Data Source=" & stDB & ";"

strSQL = " SELECT ID, NAME, PP_NO, D_1, D_2, D_3 from TBL_1 WHERE DATE = " & Me.Combobox.Value& " "

rst.Open strSQL, cnn

col = 0

row = 6
Do While Not rst.EOF
row = row + 1
col = 0
total=0
sum=0

Do While col < rst.Fields.Count
Cells(row, col + 1) = rst.Fields(col).Value
if col>=3 and col<=5
if rst.fields(col).value > 0 then
sum=sum+rst.fields(col).value
total=total+1
end if

end if
col = col + 1
Loop
if total>0 then
avg=sum/total
else
avg=0
end if
rst.MoveNext
Loop

Set rst = Nothing: Set cnn = Nothing
End Sub

``````
0

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.