[RESOLVED] Finding the Sum of the Data in a Column

[...:::ONE:::...]

Ok, I'm throwing my last idea out...

Just have a simple question.

I'm connected to an access database using VB 2008.. there is information already located in the tables. But what I am looking to do, is find the "SUM" of all the data in a certain column.

Any ideas?

If you need more info let me know.

Thanks Alot,
Chris

[BrianS]

Use the SUM SQL command?

[jmcilhinney]

If you mean that you've already populated a DataTable and you want to sum one of its DataColumns then you have two choices:

1. Call Compute on the DataTable:

Dim sum As Integer = CInt(table.Compute("SUM(SomeColumn)", Nothing))

2. Use LINQ:

Dim sum = table.AsEnumerable().Sum(Function(row) row.Field(Of Integer)("SomeColumn"))

Just note that LINQ requires .NET 3.5.

[jmcilhinney]

Here's a LINQ query-syntax version equivalent to that previous function-syntax version:

Dim sum = (From row In table.AsEnumerable() Select row.Field(Of Integer)("SomeSolumn")).Sum()

[...:::ONE:::...]

Yeah but I started to wonder if I was actually using it correctly, to I tried to think of another..

this was the original.

        sql = "SELECT sum(dstotal) FROM Table1 WHERE bq1= '" & Label44.Text & "'"
        con.Open()
        Dim cmd As New OleDb.OleDbCommand(sql, con)

        Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader
        reader.Read()
        Label44.Text = reader("dstotal")


        reader.Close()
        con.Close()

I had gotten a circular reference error.

[...:::ONE:::...]

thanks jmcilhinney for the code, i'll try it.

[jmcilhinney]

Yeah but I started to wonder if I was actually using it correctly, to I tried to think of another..

this was the original.

        sql = "SELECT sum(dstotal) FROM Table1 WHERE bq1= '" & Label44.Text & "'"
        con.Open()
        Dim cmd As New OleDb.OleDbCommand(sql, con)

        Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader
        reader.Read()
        Label44.Text = reader("dstotal")


        reader.Close()
        con.Close()

I had gotten a circular reference error.

Follow the CodeBank link in my signature and find my thread on Saving & Retrieving Data to see how to use ADO.NET to execute a scalar query. Also, take note of the other examples that show you how to use parameters to insert variables into SQL statements. Using string concatenation like that is bad.

[BrianS]

Your SQL column name return probably doesn't match your reader parameter. Try changing your SQL to this:

sql = "SELECT sum(dstotal) as dstotal FROM Table1 WHERE bq1= '" & Label44.Text & "'"

[Campion]

Your SQL column name return probably doesn't match your reader parameter. Try changing your SQL to this:

sql = "SELECT sum(dstotal) as dstotal FROM Table1 WHERE bq1= '" & Label44.Text & "'"

OR even this (as suggested by JM):

sql = "SELECT sum(dstotal) as dstotal FROM Table1 WHERE bq1= ?Whatever"

Dim cmd As New OleDb.OleDbCommand(sql, con)
cmd.parameters.AddWithValue("?Whatever", Label44.text)
cmd.Prepare

Also, I HIGHLY suggest you come up with more meaning names for your controls. If you really have 44 labels on this form, it's a wonder you can keep them straight.

[...:::ONE:::...]

Follow the CodeBank link in my signature and find my thread on Saving & Retrieving Data to see how to use ADO.NET to execute a scalar query. Also, take note of the other examples that show you how to use parameters to insert variables into SQL statements. Using string concatenation like that is bad.

Thanks jmcilhinney, I will take a look at that. I'm not too familiar with the .net structure of things, I'm normally doing interface; but, I'm getting better.

Your SQL column name return probably doesn't match your reader parameter. Try changing your SQL to this:

sql = "SELECT sum(dstotal) as dstotal FROM Table1 WHERE bq1= '" & Label44.Text & "'"

Actually I appologize, the "Circular Reference Error" was given to me with the "as dstotal" segment inserted. I tried getting rid of it, and I got a new error, haha.. so using the original code I gave you (without the "as dstotal" segment, i got the following error.

"No value given for one or more required parameters."

There are values under the "bq1 column" if that's what it's talking about

Sorry for the confusion.

[...:::ONE:::...]

Campion, thanks for the help.. I have a few more things to try.

and about the controls, i try to save time by letting vb give them their defaulted names.. Its not too confusing keeping them that way.

[BackWoodsCoder]

"No value given for one or more required parameters."

Make sure your column names in the access DB match EXACTLY what you're typing in your query, otherwise you'll receive this error.

and about the controls, i try to save time by letting vb give them their defaulted names.. Its not too confusing keeping them that way.

yah, but dear god I would hate to be the developer who maintained your code at a later point.

[...:::ONE:::...]

They are exactly the same. You have no idea how much of a pain this data base has been, ha... For example, I spent hours screwing around with coding to find out that their are certain titles to columns that I cant use, that will cause a syntax , and some other errors.

and Campion, what are u refering to when you are using "?Whatever"
Sorry

[...:::ONE:::...]

yah, but dear god I would hate to be the developer who maintained your code at a later point.

hahaha, well I would eventually take care of that... besides that's what the Visual part of VB is for if you need to distinguish controls. Just kidding.

Thanks for your help btw.

[BackWoodsCoder]

?Whatever denotes a paramter where ? represents the parameter and "Whatever" would be the name of the parameter.

[...:::ONE:::...]

I see, I'm sorry I've been up for a while now... things are coming slow to me.
Thanks for the help.

[Campion]

I see, I'm sorry I've been up for a while now... things are coming slow to me.
Thanks for the help.

Backwoods is correct. One definition of "Whatever" in English is that of a term used to describe something that could have multiple names/meanings/descriptions, etc.

[BackWoodsCoder]

well, here's something you can try. Since you're setting a string variable to hold your sql query: run your program, set a breakpoint right after the variable receives the sql string, then go to the immediate window and type in ?sql. Copy and paste the result from the immediate window into your access DB and run the query.

Does it execute in the Access DB?

[Campion]

Does it execute in the Access DB?

Assuming that he is indeed using Access, then he will need to remove the name of the parameter (not the marker) from the SQL query.

[BackWoodsCoder]

That would be true if the parameter was actually there in the sql string result from the immediate window. However, since he's setting a string variable and then setting the string variable as the command text to his sql command object, the parameter value should be populated when he types in ?sql in the immediate window.

Always is for me, it's how I debug parameterized queries when I'm having issues.

[Campion]

That would be true if the parameter was actually there in the sql string result from the immediate window.

It entirely depends on the connector and the database that you use. Some databases take a parameter name, and others don't like them.

[BackWoodsCoder]

It entirely depends on the connector that you use. Some databases take a parameter name, and others don't like them.

I think you're misunderstanding what I'm saying. The immediate window result should not include any parameters, it should include the completed sql query with the parameter being replaced by whatever value the parameter is holding.

Dim sql as String
Dim sqlCmd as New SqlCommand

sql = "SELECT * FROM SomeWhere WHERE SomeThing = @Param"
sqlcmd.Parameters.AddWithValue("@Param", 1)
sqlcmd.CommandText = sql

Now, if you executed that code and put a breakpoint after the sqlCmd.CommandText line, went to the immediate window and typed in ?sql, the result should look like

"SELECT * FROM SomeWhere WHERE SomeThing = 1"

[Campion]

I think you're misunderstanding what I'm saying.

I'm not misunderstanding you. I'm just noting that different database/connector combos may or may not take parameter names in the SQL query. MS SQL Server, MySQL do, while MS SQL Server Lite does not, and SQLite doesn't care either way. Of course, they all use different connectors as well, but could just as easily use the ODBC connector if someone was so inclined.

Access, being a light MS database, AFAIK, does not support named parameters.

[BackWoodsCoder]

Sure, it doesn't support Named parameters which is why you use the ?, but you can still name them for readability.

http://www.vbforums.com/showthread.php?t=587547&page=2

It's nothing to do with the OleDbCommand. It's the underlying OLEDB provider that decides what it supports. In this case I assume that it's for clarity. I always use parameters with names when using Access for clarity, even if they aren't used internally.

No, you can use parameters in OLEDB, I do it all the time (as I said I tested his code and it passed with no issues, after I added the semi colon to the connection string!). Unlike SQL though, you must add them in the order that they are found in the code. So if you have @Order and then @Number you must put them in that exact order.

[Campion]

Sure, it doesn't support Named parameters which is why you use the ?, but you can still name them for readability.

http://www.vbforums.com/showthread.php?t=587547&page=2

When you insert the parameters, using X.Parameters, yes. However, I was mentioning in the SQL string itself, not the actual insertion of the parameters.

However, we've veered way off topic.

[BackWoodsCoder]

Ah, yes, now i see what you're saying.

So yah, back on track....

[...:::ONE:::...]

Ok guys, I appreciate the help.. but I'm lost. Ha.

In stead of listing all my confusing code, I'll write a small example of what I want to do. I'll be straight to the point.

I've added the following references (could use linq, but I hadn't originally)
- System.Data
- System.Data.Xml

Imports System.Data
Public Class frmSumForm

    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String

    Private Sub ConnectDB()

        Dim cb As New OleDb.OleDbCommandBuilder(da)
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.mdb"

        con.Open()

        sql = "SELECT * FROM Table1"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Table1")

        con.Close()

    End Sub

    Private Sub GetSum()

        sql = "SELECT sum(dstotal) FROM Table1 WHERE bq1= '" & lblSumDisplay.Text & "'"
        con.Open()
        Dim cmd As New OleDb.OleDbCommand(sql, con)

        Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader
        reader.Read()
        lblSumDisplay.Text = reader("dstotal")


        reader.Close()
        con.Close()

    End Sub

    Private Sub frmSumForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        ConnectDB()

    End Sub

    Private Sub btnShowSum_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowSum.Click

        GetSum()

    End Sub

End Class

The error I would receive would be: "No value given for one or more required parameters."

So from what I am understanding at this point is that the way I used my parameters are wrong?

Which would be this part of the sql line?

'" & lblSumDisplay.Text & "'

I apologize for anymore confusion, and I hope the code above is written right, I kind of wrote it from the top of my head.




Thanks,
Chris

[jmcilhinney]

This is what I said back in post #7:

Follow the CodeBank link in my signature and find my thread on Saving & Retrieving Data to see how to use ADO.NET to execute a scalar query. Also, take note of the other examples that show you how to use parameters to insert variables into SQL statements. Using string concatenation like that is bad.

I gave you two pieces of advice and you've ignored both. You're still using ExecuteReader instead of ExecuteScalar and you're still using string concatenation to build your SQL statements instead of parameters.

[...:::ONE:::...]

jmcilhinney,

I'm sorry I'm from the east coast and I had been up till 10am that morning... I started looking but I had given up when I got lost looking for more info...

I had used your advice, but it didn't work so well.

I had used the code below (because I wasn't using linq) and inserted my information... is this correct?

Dim sum As Integer = CInt(ds.Tables("Table1").Compute("SUM(bq1)", Nothing))

Is this the only line of code I would need? If so, I had gotten an error.


Thank you, for the help.. my skills seem limited to me.

-Chris

BTW: I am reading the information in your code bank as we speak.

[jmcilhinney]

My original advice was based on the assumption that you already had all the data in a DataTable, in which case querying the database again is pointless. In that case yes, what you have there is basically all you need. If an error occurs and you don't provide us with the error message then there's only so much we can do. We can't read your mind.

If you are going to query the database again then, as I said, the examples in my CodeBank submission will show you how.

[...:::ONE:::...]

Ok I found what you were talking about in your code bank

here's the code for the button:

        Using connection As New SqlClient.SqlConnection("UNKNOWN")
            Using command As New SqlClient.SqlCommand("SELECT SUM(Quantity) FROM StockItem", connection)
                connection.Open()
                Dim totalQuantity As Double = CDbl(command.ExecuteScalar())

                lilSumDisplay.Text = totalQuantity
            End Using
        End Using

the "UNKNOWN" part is the connection string I need to use. But I cannot use "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.mdb"

How can I connect to the access database?

[...:::ONE:::...]

I understand that completely.

When I used the code below:

Dim sum As Integer = CInt(ds.Tables("Table1").Compute("SUM(bq1)", Nothing))

I get the following error:
"Invalid usage of aggregate function Sum() and Type: String."

The code is reading the table (by inserting an invalid column name, the error basically told me it didn't exist in the table. So by using the correct one it continued to give me the error above.)

[jmcilhinney]

Ok I found what you were talking about in your code bank

here's the code for the button:

        Using connection As New SqlClient.SqlConnection("UNKNOWN")
            Using command As New SqlClient.SqlCommand("SELECT SUM(Quantity) FROM StockItem", connection)
                connection.Open()
                Dim totalQuantity As Double = CDbl(command.ExecuteScalar())

                lilSumDisplay.Text = totalQuantity
            End Using
        End Using

the "UNKNOWN" part is the connection string I need to use. But I cannot use "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.mdb"

How can I connect to the access database?

The answer to that question is in the very first paragraph of that CodeBank thread.

[jmcilhinney]

I understand that completely.

When I used the code below:

Dim sum As Integer = CInt(ds.Tables("Table1").Compute("SUM(bq1)", Nothing))

I get the following error:
"Invalid usage of aggregate function Sum() and Type: String."

The code is reading the table (by inserting an invalid column name, the error basically told me it didn't exist in the table. So by using the correct one it continued to give me the error above.)

The error message is telling you that you're trying to sum a column of strings. Of course you can't sum strings. Summing is a mathematical operation so it's performed on numbers. If this column is supposed to contain numbers then is it a text data type and if it doesn't contain all numbers then how can you possibly sum it?

[...:::ONE:::...]

jmcilhinney,

I feel retarded. If I hadn't tried to rush, and read through my code more closely. I would have seen that I had..

- To use an oledb connection with an oledb command
- That in my command string I hadn't used "bq1" after SUM
- and that I was getting my information FROM Table1.

        Using connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Documents and Settings\Chris\Desktop\Personal Advisor\Database\TestBase.mdb")
            Using command As New OleDb.OleDbCommand("SELECT SUM(bq1) FROM Table1", connection)
                connection.Open()
                Dim totalQuantity As Double = CDbl(command.ExecuteScalar())

                Label44.Text = totalQuantity
            End Using
        End Using
    End Sub

Thanks for your help, I shouldn't have any more problems. Your reference to your code bank was very helpful.

I need to pay more attention ;]



Thanks again,
Chris

[jmcilhinney]

All's well what ends well.