Results 1 to 6 of 6

Thread: [RESOLVED] SQL 1 query two distinct elements?

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Jun 2015
    Posts
    748

    Resolved [RESOLVED] SQL 1 query two distinct elements?

    Some people can do some very clever things with sql, but Iím not sure if it can do this in one query?
    (I am working with sqllite here)

    I want three bits of information, distinct file_path, total instances it appears, and total distinct users that had it.

    The first two I can get in one shot with something like

    Code:
    select distinct(file_path) as f, count(guid) as c from main group by f order by c desc
    currently to get the distinct users count for each file, I have to do an additional step something like

    Code:
    For Each file_path In distinct_paths
            Set rs = cn.Execute("select count(distinct guid) as x From main where file_path ='" & file_path & "'")
    anyone know if there is some one sql query without having to add a second stage of vb code to process?

    example data
    Code:
    file_path     guid
    1.txt           1
    1.txt           1
    1.txt           2
    
    end result  would be 
    
        file,count,users
        1.txt,  3,  2
    Last edited by dz32; Jul 29th, 2022 at 05:05 AM.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,923

    Re: SQL 1 query two distinct elements?

    maybe someting like this:
    Code:
    select file_path as f, count(guid) as c, count(distinct guid) as u from main Group by file_path
    Untested..

    -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
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    7,803

    Re: SQL 1 query two distinct elements?

    Quote Originally Posted by techgnome View Post
    maybe someting like this:
    Code:
    select file_path as f, count(guid) as c, count(distinct guid) as u from main Group by file_path
    Untested..

    -tg
    Yep, that's correct. Tested in SQLite using VB.Net:-
    Code:
    Imports SQLite
    
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Dim path As String = "d:\SQLiteDB.DB"
    
            If IO.File.Exists(path) Then IO.File.Delete(path)
    
            Dim db As New SQLiteConnection(path)
    
            db.CreateTable(Of FileData)()
    
            Dim data As New List(Of FileData)
    
            data.Add(New FileData("1.txt", 1))
            data.Add(New FileData("1.txt", 1))
            data.Add(New FileData("1.txt", 2))
    
            data.Add(New FileData("2.txt", 6))
            data.Add(New FileData("2.txt", 9))
            data.Add(New FileData("2.txt", 1))
    
            For Each d In data
                db.Insert(d)
            Next
    
            Dim agData = db.Query(Of AggregateData)("Select FilePath, count(filepath) as pathcount, count(distinct UserID) as UserCount from FileData group by FilePath")
    
            Debug.WriteLine($"{"File".PadRight(10)} {"Count".PadRight(10)} {"Users".PadRight(10)}")
    
            For Each d In agData
                Debug.WriteLine($"{d.FilePath.PadRight(10)} {d.PathCount.ToString.PadRight(10)} {d.UserCount.ToString().PadRight(10)}")
            Next
    
    
        End Sub
    End Class
    Public Class AggregateData
    
        Public Property FilePath As String
        Public Property PathCount As Integer
        Public Property UserCount As Integer
    End Class
    
    Public Class FileData
        Public Sub New()
    
        End Sub
        Public Sub New(filePath As String, userID As Integer)
            Me.FilePath = filePath
            Me.UserID = userID
        End Sub
        Public Property FilePath As String
        Public Property UserID As Integer
    End Class
    Output:-
    Code:
    File       Count      Users     
    1.txt      3          2         
    2.txt      3          3
    This is the data used:-
    Code:
     1.txt, 1
     1.txt, 1
     1.txt, 2
     2.txt, 6
     2.txt, 9
     2.txt, 1
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Jun 2015
    Posts
    748

    Re: [RESOLVED] SQL 1 query two distinct elements?

    yuppers this did the trick thanks guys

  5. #5
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Posts
    4,122

    Re: [RESOLVED] SQL 1 query two distinct elements?

    Keep in mind that SQL has some very weird syntax esp. with aggregate functions like MIN, MAX and COUNT in your case.

    Take for instance these ones
    1. COUNT(guid)
    2. COUNT(DISTINCT guid)
    3. COUNT(*)

    These are three *different* aggregate functions, don't be fooled by their names "overlapping" a bit. Also in COUNT(*) the star has nothing to do with the star in SELECT * FROM table -- it's just an unfortunate artefact of a bonkers syntax. COUNT(*) should have been COUNT_ROWS() or similar with no stars and not coinciding with COUNT(guid).

    Don't use COUNT(guid) unless you want the number of rows where column `guid` is NOT NULL. This is what this aggregate function does and most people do not realize that it's slower than COUNT(*) but keep on using it on non-nullable columns for no apparent reason. Just use COUNT(*) already!

    cheers,
    </wqw>

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Jun 2015
    Posts
    748

    Re: [RESOLVED] SQL 1 query two distinct elements?

    interesting thanks I was not aware of that. I have noticed anomalies at times.

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