Count register with a condition

Posted on 2005-04-18
Medium Priority
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
  • 2
LVL 34

Expert Comment

by:Brian Crowe
ID: 13809996
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

ID: 13810112
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

ID: 13810280

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


LVL 14

Accepted Solution

amyhxu earned 2000 total points
ID: 13811042
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
How to fix display issue, screen flickering issue when I plug in power cord to the machine. Before I start explaining the solution lets check out once the issue how it looks like after I connect the power cord. most of you also have faced this…
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…
Suggested Courses
Course of the Month8 days, 11 hours left to enroll

621 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