Count register with a condition

Posted on 2005-04-18
Last Modified: 2010-04-23
I have a table with 2 fields:  IDpierna and IDaltura
I need to know how many registrations they exist with the same IDpierna and IDaltura.
For ejempo:

IDpierna   IDAltura
   1           1
   1           1
   1           1
   1           2
   1           2
   1           3

He/she would need to obtain:
For IDpierna = 1
IDaltura =1 are 3 registers.
IDaltura = 2 are 2 registers.
IDaltura =3 are 1 register.

I also need to keep in variables The IDpierna that have register and for each IDpierna that has registrations the IDaltura that in turn have registers..
The program should show the  data

IDpierna = x
     IDaltura A1 = x1
     IDAltura A2 = x2
     IDaltura A3 = x3

Being x the regiser IDpierna = 1 and A1 the registrations with IDpierna = 1 and IDaltura = 1, A2 the registrations with IDpierna = 1 and IDaltura = 2.......
And this way for all the table.


Question by:miguelmam
    LVL 34

    Expert Comment

    by:Brian Crowe
    I had a hard time following but if you want the count of IDAltura in your table...

    dim dv as new dataview(myTable)
    dv.rowfilter = "IDAltura = 1"
    dim x1 as integer = dv.count

    Depending on yoru datasource, this might be easier to do on the server side.
    LVL 14

    Expert Comment

    Another way is to write a query to get the counts:
    first wirte a query to get the counts for different IDpierna
      FROM myTable
      GROUP BY IDpierna
    and create a dataset ds1 out of it, or use a DataReader to read it row by row.

    Then for each row from above, use the following query to get the counts for different IDAltura:
      FROM myTable
      GROUP BY IDpierna, IDAltura
    and use a DataReader to get all the rows.

    Author Comment


    Can you post all the code?, including create dataset and datareader


    LVL 14

    Accepted Solution

    Imports System.Data.SqlClient

    Public Class Form1
        Inherits System.Windows.Forms.Form

    #Region " Windows Form Designer generated code "

        Public Sub New()

            'This call is required by the Windows Form Designer.

            'Add any initialization after the InitializeComponent() call

        End Sub

        'Form overrides dispose to clean up the component list.
        Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
            If disposing Then
                If Not (components Is Nothing) Then
                End If
            End If
        End Sub

        'Required by the Windows Form Designer
        Private components As System.ComponentModel.IContainer

        'NOTE: The following procedure is required by the Windows Form Designer
        'It can be modified using the Windows Form Designer.  
        'Do not modify it using the code editor.
        Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
        Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
        <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
            Me.TextBox1 = New System.Windows.Forms.TextBox
            Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
            Me.TextBox1.Location = New System.Drawing.Point(16, 16)
            Me.TextBox1.Multiline = True
            Me.TextBox1.Name = "TextBox1"
            Me.TextBox1.Size = New System.Drawing.Size(464, 256)
            Me.TextBox1.TabIndex = 0
            Me.TextBox1.Text = ""
            Me.SqlConnection1.ConnectionString = "workstation id=LAP001;packet size=4096;integrated security=SSPI;data source=abc" & _
            "persist security info=False;initial catalog=Development"
            Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
            Me.ClientSize = New System.Drawing.Size(496, 293)
            Me.Name = "Form1"
            Me.Text = "Form1"

        End Sub

    #End Region

        Dim ds As DataSet

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


            Dim s As String
            Dim IDpierna As Integer
            Dim countIDpierna As Integer
            For Each dRow As DataRow In ds.Tables("counts").Rows
                If dRow("IDpierna") <> IDpierna Then
                    s &= "IDpierna = " & dRow("IDpierna") & vbCrLf
                End If
                s &= "    IDAltura A" & dRow("IDAltura") & " = " & dRow("count") & vbCrLf
                IDpierna = dRow("IDpierna")

            TextBox1.Text = s

        End Sub

        Private Sub FillDataSet()

                Dim sda As New SqlDataAdapter("SELECT IDpierna,IDAltura,COUNT(*) As count FROM myTable GROUP BY IDpierna,IDAltura ORDER BY IDpierna,IDAltura", SqlConnection1.ConnectionString)
                ds = New DataSet
                sda.Fill(ds, "counts")
            Catch ex As Exception
            End Try

        End Sub

    End Class


    The only change you need to do is to create your own sqlConnection1, basically the connectionString is changed, which is this line:
            Me.SqlConnection1.ConnectionString = "workstation id=LAP001;packet size=4096;integrated security=SSPI;data source=abc" & _
            "persist security info=False;initial catalog=Development"

    I created a table with two columns and the following data:
    IDpierna  IDAltura
    1      1
    1      1
    1      1
    1      2
    1      2
    1      3
    2      1
    2      1
    2      2
    2      3
    2      4
    2      4
    3      3
    3      3
    3      4

    run the program and get this result:
    IDpierna = 1
        IDAltura A1 = 3
        IDAltura A2 = 2
        IDAltura A3 = 1
    IDpierna = 2
        IDAltura A1 = 2
        IDAltura A2 = 1
        IDAltura A3 = 1
        IDAltura A4 = 2
    IDpierna = 3
        IDAltura A3 = 2
        IDAltura A4 = 1


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
    The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now