Results 1 to 3 of 3

Thread: [RESOLVED] Count distinct an sql server table column according to another column

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Oct 2012
    Location
    Caracas, Venezuela
    Posts
    121

    Resolved [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

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Oct 2012
    Location
    Caracas, Venezuela
    Posts
    121

    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
  •  



Click Here to Expand Forum to Full Width