-
Feb 28th, 2018, 01:14 PM
#1
Thread Starter
Lively Member
[RESOLVED] Count distinct an sql server table column according to another column
Suppose I have a two columns SQL Server table, A & B.
Cells in A contains any one of eight options according to the choice a user made from a combo box.
Cells in B also contains any one of eight options according to the choice a user made from another combo box.
It is possible that an option in B be repeated in different alternative in A. I mean:
Column A Column B
1 Y
2 Y
1 X
1 Y
2 Z
2 Z
1 y
I did the code that is shown below. The problem is that, when counting distinct in Column B, it returns 3, being one X, one Y and one Z.
What I need is to count distinct, complying with some requirements, but according or depending on cells in A. I mean, one Y to 1, one X to 1, one Z to 2 and one Y to 2, which would add 4.
I do not have an idea about how to deal with this.
I appreciate any help.
Thank you
Nelson Calabria
HTML Code:
Private Function COUNTTOSUBTOTAL()
Me.Cursor = Cursors.WaitCursor
'BUSQUEDA
Dim RefToLook As String = ""
Dim TipoToLook As String = ""
Dim SubTotalCount As DistinctValues
'What to look for
RefToLook = GlobalVariables.ReferenciaID
TipoToLook = "Directo" 'GlobalVariables.TipoID
'Set up connection string
Dim cnString As String
cnString = GlobalVariables.connString
'Create connection
Dim conn As SqlConnection = New SqlConnection(cnString)
conn.Open()
'Statement to look for
Dim SqlStr
SqlStr = "Select Count(Distinct SubGrupo) From PARTIDAS Where Referencia= '" & RefToLook & "' And Tipo= '" & TipoToLook & "'"
Try
Using cmd As New SqlCommand(SqlStr, conn)
SubTotalCount.DSubGrupo = Convert.ToInt32(cmd.ExecuteScalar())
End Using
conn.Close()
conn = Nothing
Catch ex As Exception
Throw ex[B][/B]
End Try
COUNTTOSUBTOTAL = SubTotalCount
End Function
-
Feb 28th, 2018, 01:39 PM
#2
Re: Count distinct an sql server table column according to another column
Use an inner query to select the distinct rows, then get the count of those:
Code:
create table sampleTable (
ColumnA int,
ColumnB varchar(3),
ColumnC varchar(3)
)
go
insert into sampleTable (ColumnA, ColumnB, ColumnC) Values (1, '1', 'Y')
insert into sampleTable (ColumnA, ColumnB, ColumnC) Values (2, '2', 'Y')
insert into sampleTable (ColumnA, ColumnB, ColumnC) Values (1, '1', 'X')
insert into sampleTable (ColumnA, ColumnB, ColumnC) Values (1, '1', 'Y')
insert into sampleTable (ColumnA, ColumnB, ColumnC) Values (2, '2', '2')
insert into sampleTable (ColumnA, ColumnB, ColumnC) Values (2, '2', '2')
insert into sampleTable (ColumnA, ColumnB, ColumnC) Values (1, '1', 'Y')
go
select count(distinct ColumnC) from sampleTable -- original result
select count(*) from (select distinct ColumnA, ColumnC from sampleTable) D -- new result
go
drop table sampleTable
go
-tg
-
Mar 1st, 2018, 09:21 PM
#3
Thread Starter
Lively Member
Re: Count distinct an sql server table column according to another column
Thank you so much, techgnome.
Your recommendation was great. It did the job.
Nelson Calabria
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|