dcsimg
Results 1 to 12 of 12

Thread: SQL count the number of DISTINCT rows

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2014
    Posts
    52

    SQL count the number of DISTINCT rows

    Is there any way to count the number of DISTINCT rows? I have tried unsuccessfully


    Code:
    Set rs22 = ObjConnection.Execute("SELECT  COUNT( *)    FROM (SELECT DISTINCT * " & rs2 & " AS T2)")  ' Get nb of rows from recordset
    Thanks
    Avi

  2. #2
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,277

    Re: SQL count the number of DISTINCT rows

    whats wrong with it? cant see any obvious mistake (well, depending on what rs2 is), rs22(0) should be the number you want.
    maybe set a breakpoint on that specific line and post the result of
    ? "SELECT COUNT( *) FROM (SELECT DISTINCT * " & rs2 & " AS T2)"
    i.e. the actual SQL statement.

  3. #3
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,423

    Re: SQL count the number of DISTINCT rows

    this is the syntax
    Code:
    SELECT COUNT(DISTINCT(CodEmisor)) from FONDOS
    What are you concatenating there? what's inside variable rs2 ? Is it a string containing something like " FROM <TABLE>" or a recordset?

  4. #4
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,171

    Re: SQL count the number of DISTINCT rows

    NO, jcis, OP is not looking for distinct codEmisor(or any one field name), but distinct RECORDS....for example, if OP has 7 fields in a record, and just ONE of those fields has a different value than another record's, yet all the other fields are identical to that other record's same fields, then that RECORD would be distinct. At least that is the way I read it.

  5. #5
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,171

    Re: SQL count the number of DISTINCT rows

    I guess a loop through all fields in each record, concatenating their 'values' into, say a STRING variable, and if any STRING is different from any other, then it would be distinct. I think.

  6. #6
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,423

    Re: SQL count the number of DISTINCT rows

    Ok you're right, then the query the OP posted is the one to use, the only reason it is not working is that it needs an alias for the table, this is the syntax:
    Code:
    SELECT COUNT(1) FROM (SELECT DISTINCT Field1, Field2 FROM TableName) T
    There is still a possibility that variable he's is concatenating (rs2) is not even a string or has invalid data inside. I hope rs2 is not a recordset, it would be total non-sense using a recordset like that.

  7. #7
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,171

    Re: SQL count the number of DISTINCT rows

    Here's a short example with a table that looks like this:
    Attachment 114003
    NOTE that the 3rd and 5th lines are DUPES (All records in each of the 4 fields are identical)
    Code:
    Option Explicit
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Private Sub Command1_Click()
        Dim ssql As String
        Dim x As Integer
        Dim recordinfo As String
        rs.Open "table1", cnn, adOpenKeyset, adLockPessimistic, adCmdTable
        ssql = "select * from table1"
        Set rs = cnn.Execute(ssql)
        Do While Not rs.EOF
            recordinfo = CStr(rs!Price) & CStr(rs!qty) & rs!Desc & CStr(rs!mdate)
            List1.AddItem recordinfo
            rs.MoveNext
        Loop
        idDupes
    End Sub
    Private Sub idDupes()
    'Remove Duplicates
    Dim i As Long
    Dim j As Long
        With List1
            For i = 0 To .ListCount - 1
                For j = .ListCount To (i + 1) Step -1
                    If .List(j) = .List(i) Then
                         List1.ListIndex = j
                         List2.AddItem (List1.Text)
                        .RemoveItem j
                    End If
                Next
            Next
        End With
    End Sub
    Private Sub Form_Load()
        Dim ConnectionString As String
        Set cnn = New ADODB.Connection
        Set rs = New ADODB.Recordset
        cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
        cnn.ConnectionString = "User ID=Admin;password= ;" & " Data Source=" & App.Path & "\db2.accdb;"
        cnn.Open ConnectionString
        rs.CursorLocation = adUseClient
    End Sub
    It checks each set of fields in each record......first it adds ALL records into list1, then deletes the duplicate (not distinct) and puts it into list2.
    So, the result would be all DISTINCT records would be in list1 and any dupes would be in list2. Hence, list1.listcount would give OP a NUMBER of distinct records.

  8. #8
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,277

    Re: SQL count the number of DISTINCT rows

    Quote Originally Posted by SamOscarBrown View Post
    Here's a short example with a table that looks like this:
    Attachment 114003
    NOTE that the 3rd and 5th lines are DUPES (All records in each of the 4 fields are identical)
    Code:
    Option Explicit
    ...
    It checks each set of fields in each record......first it adds ALL records into list1, then deletes the duplicate (not distinct) and puts it into list2.
    So, the result would be all DISTINCT records would be in list1 and any dupes would be in list2. Hence, list1.listcount would give OP a NUMBER of distinct records.
    hmmm, why going over complicated when it can be done in one sql statement? as jcis already noted with the table alias your example table works perfectly nice with
    Code:
    select count(*) from (select distinct * from table1) T1
    in MS-SQL, i guess its similar in Access.

    Quote Originally Posted by jcis View Post
    I hope rs2 is not a recordset, it would be total non-sense using a recordset like that.
    i also thought about the same...

  9. #9
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,171

    Re: SQL count the number of DISTINCT rows

    DIGI SAID: "hmmm, why going over complicated when it can be done in one sql statement? as jcis already noted with the table alias your example table works perfectly nice with"
    1-I don't know.
    2-I didn't see jcis post before I did mine as I was checking MY code.

    Sorry to have upset you.

  10. #10
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,277

    Re: SQL count the number of DISTINCT rows

    Quote Originally Posted by SamOscarBrown View Post
    DIGI SAID: "hmmm, why going over complicated when it can be done in one sql statement? as jcis already noted with the table alias your example table works perfectly nice with"
    1-I don't know.
    2-I didn't see jcis post before I did mine as I was checking MY code.

    Sorry to have upset you.
    ok understood. i'm not upset and i'm sorry if i have upset you. everythings fine
    i just did'nt understand why you put something into code that the sql engine can do.

  11. #11
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,171

    Re: SQL count the number of DISTINCT rows

    Didn't know it could...until now...thanks for the input (and jcs). I tested like you posted, DOES work just fine).

  12. #12
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,493

    Re: SQL count the number of DISTINCT rows

    Some possibly needed background on this - http://www.vbforums.com/showthread.p...inct-left-join

    Meanwhile we're kind of stuck until the OP comes back and reports what is working and what isn't.


    -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??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width