Solved

# Count register with a condition

Posted on 2005-04-18
Medium Priority
174 Views
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.

thanks,

0
Question by:miguelmam
• 2

LVL 34

Expert Comment

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.
0

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
SELECT COUNT(*)
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:
SELECT COUNT(*)
FROM myTable
GROUP BY IDpierna, IDAltura
and use a DataReader to get all the rows.
0

Author Comment

ID: 13810280
amyhxu

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

Thank,

0

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()
MyBase.New()

'This call is required by the Windows Form Designer.
InitializeComponent()

'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
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
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.SuspendLayout()
'
'TextBox1
'
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 = ""
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "workstation id=LAP001;packet size=4096;integrated security=SSPI;data source=abc" & _
"persist security info=False;initial catalog=Development"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(496, 293)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False)

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

FillDataSet()

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")
Next

TextBox1.Text = s

End Sub

Private Sub FillDataSet()

Try
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
MsgBox(ex.ToString)
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

0

## Featured Post

Question has a verified solution.

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

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