locked
How to use Count,Calculate sum(), Caluculate Max() in VB.NET?

    Question

  • Hi

    Thanks in Advance.

    In VFP9 we can use the followings and How to do it with vb.net?

    COUNT FOR(wk_code='Inj ') TO abcd5
    CALCULATE SUM(total_hr) FOR (emp_id=epidi1) TO tot1
    CALCULATE MAX(en_date) FOR((emp_id=epidi1) TO etydt

    --------------------------------------------
    In Vb.Net

    Dim dbnd1 As New BindingSource
    dbnd1.DataSource = DTB4 'Datatable for SEC-ID's
    ?????????


    U.PARANTHAMAN
    Tuesday, March 31, 2009 7:38 AM

Answers

  • In addtion, you can achieve the equivalent calculation via DataTable.Compute method.

    Imports System.Data.OleDb
    
    Public Class Form1
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'Firstly fill data from database table into a DataTable object
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT Username FROM UserTable", con)
            con.Open()
            Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
            Dim myDataTable As DataTable = New DataTable()
            myDA.Fill(myDataTable)
            con.Close()
            con = Nothing
            'Then you can use myDataTable.Compute() to do something
        End Sub
    
    End Class


    A DataTable has a Compute() method to derive an aggregate value for a column expressed in the following format.

    DataTable.Compute("AggregateFunction(DataColumn)", "condition|Nothing|null")

    The Compute() method is passed two arguments in a comma-separated list of string values. The first argument is the name of an AggregateFunction() which has the name of a DataColumn included within paretheses; this is the DataSet column to which the aggregate function is applied. The second argument restricts the DataRows that are accessed. If all rows of the column are used in the function, there are no restrictions, so a null value (or the keyword Nothing) is passed. Otherwise, a condition is supplied to identify which rows are selected.

    Available aggregate functions include those shown in the following table.

    Avg()

    The average of values in a column

    Count()

    The number of rows (values) in a column

    Max()

    The largest value in a column

    Min()

    The smallest value in a column

    StDev()

    The standard deviation of values in a column

    Sum()

    The sum of values in a column

    Var()

    The statistical variance of values in a column


    DataTable.Compute method http://msdn.microsoft.com/en-us/library/system.data.datatable.compute.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Paramu Wednesday, April 01, 2009 12:01 PM
    Wednesday, April 01, 2009 11:08 AM

All replies

  • Hi Paramu,

    In VB.NET, you can execute T-SQL statement as below code sample to achieve the equivalent features.

    Prerequisites: Drag&drop Button1, Button2 and Button3 onto Form1.

    Imports System.Data.OleDb
    
    Public Class Form1
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT COUNT(wk_code) FROM UserTable Where wk_code='Inj'", con)
            con.Open()
            Dim abcd5 As Int32 = cmd.ExecuteScalar
            MessageBox.Show(abcd5)
            con.Close()
            con = Nothing
        End Sub
    
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=E:\myDB.mdb")
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT SUM(total_hr) FROM UserTable Where emp_id=epidi1", con)
            con.Open()
            Dim tot1 As Int32 = cmd.ExecuteScalar
            MessageBox.Show(tot1)
            con.Close()
            con = Nothing
        End Sub
    
        Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=E:\myDB.mdb")
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT MAX(en_date) FROM UserTable Where emp_id=epidi1", con)
            con.Open()
            Dim etydt As Int32 = cmd.ExecuteScalar
            MessageBox.Show(etydt)
            con.Close()
            con = Nothing
        End Sub
    
    End Class

    COMPUTE (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms181708(sql.90).aspx

    { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM }


    Best regards,
    Martin Xie
    Wednesday, April 01, 2009 10:54 AM
  • In addtion, you can achieve the equivalent calculation via DataTable.Compute method.

    Imports System.Data.OleDb
    
    Public Class Form1
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'Firstly fill data from database table into a DataTable object
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT Username FROM UserTable", con)
            con.Open()
            Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
            Dim myDataTable As DataTable = New DataTable()
            myDA.Fill(myDataTable)
            con.Close()
            con = Nothing
            'Then you can use myDataTable.Compute() to do something
        End Sub
    
    End Class


    A DataTable has a Compute() method to derive an aggregate value for a column expressed in the following format.

    DataTable.Compute("AggregateFunction(DataColumn)", "condition|Nothing|null")

    The Compute() method is passed two arguments in a comma-separated list of string values. The first argument is the name of an AggregateFunction() which has the name of a DataColumn included within paretheses; this is the DataSet column to which the aggregate function is applied. The second argument restricts the DataRows that are accessed. If all rows of the column are used in the function, there are no restrictions, so a null value (or the keyword Nothing) is passed. Otherwise, a condition is supplied to identify which rows are selected.

    Available aggregate functions include those shown in the following table.

    Avg()

    The average of values in a column

    Count()

    The number of rows (values) in a column

    Max()

    The largest value in a column

    Min()

    The smallest value in a column

    StDev()

    The standard deviation of values in a column

    Sum()

    The sum of values in a column

    Var()

    The statistical variance of values in a column


    DataTable.Compute method http://msdn.microsoft.com/en-us/library/system.data.datatable.compute.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Paramu Wednesday, April 01, 2009 12:01 PM
    Wednesday, April 01, 2009 11:08 AM
  • MARTIN ....YOU ARE GIVING WONDERFUL SERVICES.!!!!!
    THANKS TO THOSE KINDNESS...!!!!!!!


    U.PARANTHAMAN
    Wednesday, April 01, 2009 12:01 PM
  • Hi Paramu,

    You're welcome! Glad to help you :)


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, April 02, 2009 4:51 AM
  • Martin,
    Excellent advice on the DataTable.Compute method.  I have a further question.  How would you count for two columns and a condition?
    Friday, May 15, 2009 7:49 PM
  • Gary,

    In the way you ask this simple as

    \\\
    If myCondition then
         Dim result = MyTable.Rows(0).items(0) + MyTable.Rows(0).Items(1)
    end if
    ///
    This counts the first column and second column in the first row.

    However, I have the idea that you mean something different.

    Cor

    Sunday, May 17, 2009 10:01 AM
  • I have two columns: category_name and Weekday_name.  I want to count how many category "A" for weekday "Monday" there are in the table.  The datatable.compute method Martin gave only shows counting for one column and one condition.  Will this method allow for more than one column and more than one condition?  What is the syntax?
    Thanks.

    Tuesday, May 19, 2009 3:58 PM
  • Did you get my response and question?  It has been a couple of days, so I'm just checking.
    Gary
    Friday, May 22, 2009 2:06 PM
  • I have two columns: category_name and Weekday_name.  I want to count how many category "A" for weekday "Monday" there are in the table

    Hi Gary,

    I think this code snippet is what you want. Please give it a try.

            Dim countObject As Object = myDataTable.Compute("Count(category)", "category = 'A' And weekday = 'Monday'")

            MessageBox.Show(countObject.ToString)


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Send us any feedback you have about the help from MSFT at fbmsdn@microsoft.com.
    Monday, May 25, 2009 4:37 AM
  • Martin,
    That worked.  Thanks for your great help.
    Gary
    Tuesday, May 26, 2009 2:36 PM
  • Martin,
    When using the DataTable.Compute(Sum) method, I get "Conversion from type 'DBNull' to type 'Integer' is not valid" error when the column is empty.  How do I fix this?
    Thanks again.
    Tuesday, June 02, 2009 8:21 PM
  • Hi Gary,

    1. Please try:
    myDataTable.Compute(
    "Sum(QualityColumn)", "")
    myDataTable.Compute(
    "Sum(QualityColumn)", "QualityColumn IsNot Nothing")

    2. When retrieve data from database, you can consider avoiding/filtering the Null values via Where clause such as "Select * from Table1 Where QualityColumn is not Null", then fill data into DataTable.

    3. Check this similar issue for reference.
    http://forums.asp.net/t/1323498.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, June 04, 2009 11:21 AM
  • Martin,
    I have tried every combination of the Null advice you gave above, but can't get the DBNull error to stop.  Here is a snippet of my code.  Maybe you can help?

    CountSun = db.Compute("Sum(WDTotal)", "WDName='Sunday'")

    When there is no Sunday in the column, I get the DBNull error.

    Thanks again.
    Gary
    Friday, June 05, 2009 4:40 PM
  • Gary, for your case, you have a problem with your field type . [CountSun or WDTotal or WDName]

    ----------------------------------------------------------------------------
    But Iam using {Fields are strings & numerals}

    Dim
    countObject As Object = DTB31.Compute("count(itm_description)", "itm_description='" & chkitm & "' and unit_measure='" & uom & "'")

    ----------------------------------------------------
    Try with the following
    -----------------------------------------------------

    Enum enumObjectType

    StrType = 0

    IntType = 1

    DblType = 2

    End Enum

     

    Public Function CheckDBNull(ByVal obj As Object, _

    Optional ByVal ObjectType As enumObjectType = enumObjectType.StrType) As Object

    Dim objReturn As Object

    objReturn = obj

    If ObjectType = enumObjectType.StrType And IsDBNull(obj) Then

    objReturn = ""

    ElseIf ObjectType = enumObjectType.IntType And IsDBNull(obj) Then

    objReturn = 0

    ElseIf ObjectType = enumObjectType.DblType And IsDBNull(obj) Then

    objReturn = 0.0

    End If

    Return objReturn

    -------------------------------------------------------------------------------------------


    U.PARANTHAMAN

    End Function

    Saturday, June 06, 2009 9:44 AM
  • Paramu,
    Can the Dim CountObject As Object line be used alone to fix problem, or does the Public Function have to go along with it?

    In the Dim CountObject can you further explain what "chkitm", "unit_measure" and "uom"?  I don't see how those tie in with my variables.

    How does your Public Function tie in with my variables?
    Gary
     
    Sunday, June 07, 2009 3:38 AM
  • Gary, it seems to be,

    1. Try with

    Dim CountSun as object= db.Compute("Sum(WDTotal)", "WDName='Sunday'") [must solve your problem]

    2. Check "WDTotal" is string or numerals & "WDName" also string or numerals...

    3. Suppose if all the above are perfect, then you have to check for your date field null values
    If your date is null, you cannot convert the name of the day..is it not?




    U.PARANTHAMAN
    Sunday, June 07, 2009 5:46 AM
  • Paramu,

    After implementing your statement on my variables, I get the below error on my next statement:
    Dim AvgSun as object = CountSun / CountWDTot

    error:
    Operator '/' is not defined for type 'DBNull' and type 'Long'.

    Somehow, I need to filter out all DBNull.

    Gary
    Sunday, June 07, 2009 6:19 PM
  • Martin,

    I have a hw on a DataSet application, it is required to (1) use DataReader to populate two types of data into the ComboBox on the interface.  then (2) create a DataSet that contains all teh records retrieved from the voting record data base table.  (3) use the Binding Navigator control to browse and modify records in the DataSet.

    the last question is to use DataTable.Compute() method to calculate an aggregate funtion against the records in the DataTable.  I created a TextBox control at the bottom of the Form.  The TextBox is used to displau the result of the function whenever the First ComboBox (which is contained the populated data in the (1)  )  The Scenario is about the voting records, the First ComboBox contains the BillNumber, second ComboBox contains CongressPerson ID, third ComboBox is for vote (Yes/No).  Then then TextBox. 

    if there are mor Yes votes than Not votes the status (showed in TextBox) should be "Pass" otherwise should be "FAil".

     

    I've have completed all those steps then got stuck on the DataTable.  I dont know whether to put the codes on Form Load or the First ComboBox_SelectedIndexChanged, if so then what's next? Do I need to do like above as you said?  Please give me some help.  Thank you.

    Sunday, April 10, 2011 1:29 PM
  • Hi Martin,

    I am novice in VB.net and I am trying to make a windows.exe program using Visual Express 2008.

    I am making my own 10 pin bowling program, My problem at the moment is how to calculate the sum of a several fields and put it on another field.

    I have several tables, I am picking up values from the table named - Games.

    Table: Games

    Fields:

    GameDate

    BowlerID

    TeamID

    Games1

    Games2

    Games3

    TotalPlayed

    How can I sum all the scores in Games1 or Games2 or Games3 where TeamID ="1" or ... and GameDate="24/01/2012"?

    Your help is very much appreciated.

    Thursday, March 01, 2012 6:38 AM
  • I suggest you ask this in a new thread in the VB General forum:

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/threads


    Armin


    Thursday, March 01, 2012 1:07 PM