SQL Select Count Distinct with WHERE clause

Question

Aim: We have a database with UserID and SiteID if they userID/SiteID match then they can see details.

Thought: I have tried using the Count(Distinct...) to see if it exists. Then if it returns a value that is not zero I know that they can see the details.

Is this the best way?

Issue: Trying to use this way I can not get the WHERE clause to work.

Error: Errors vary am I have played but essentially says I can not have a WHERE clause, is this correct?

Code:

Dim ConnectionString As String = System.Configuration.ConfigurationManager.ConnectionStrings("mySQL").ToString()
                Using connection = New SqlConnection(ConnectionString)
                    Using command As New SqlCommand("Select Count(Distinct(UserID)) FROM tblTable where UserID= '" & Session.Item("UserID").ToString & "'", connection)

                        connection.Open()
                        Dim result = command.ExecuteScalar()

                        lblResult.Text = result 

                        connection.Close()
                    End Using
                End Using

I would declare a variable of `string` to use, to build up the query you are expecting to execute. Then you can use this for debugging purposes. — Squirrel5853, Jan 14 '14 at 12:38
What error do you get with this query, and what db are you using ? — Laurent PerrucheJ, Jan 14 '14 at 12:44
Just a side note: you should use a parameterized query, not build a complete SQL string (SQL injection, performance). — Werner Henze, Jan 14 '14 at 12:46
Nice thought @WernerHenze , LaurentPerrucheJ has put this in the code I have accepted as the answer. — indofraiser, Jan 14 '14 at 14:10
@indofraiser I do not see an answer from LaurentPerrucheJ. I do not see an answer that uses parameterized queries (see for example http://stackoverflow.com/questions/17509169/parameterized-queries-vs-sql-injection). — Werner Henze, Jan 14 '14 at 14:30
Your right, I was looking at the below assuming it was his code but it is code by Lawrence Thurman . The code you link to is good but I can already to a loop. I was hoping to see if something exists or not effectively without a counter. As soon as I add a counter it defeat the object. — indofraiser, Jan 14 '14 at 15:06

score 1 · Accepted Answer · answered Jan 14 '14 at 13:10

I tried your code and I do not see where you are having an issue, unless your Session.Item("UserId") is not being set properly. I do not have a session so I improvised in a console application. I am trying to figure out what exactly you are trying to do because the Count(Distinct(UserId)) will always return 1. Since you are asking for a specific userId, then asking to distinct it and then count it. so 1 user returns 100 times, distinct give you the only user in the list, so 1 returns.

 Dim ConnectionString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ToString()
    Using connection = New SqlConnection(ConnectionString)

        Using command As New SqlCommand()
            Dim userId As String = "3"
            command.Connection = connection
            command.CommandText = "Select Count(Distinct(UserId)) FROM tblTable _
                                      where UserId= '" & userId & "'"

            connection.Open()
            Dim result = command.ExecuteScalar()

            Console.WriteLine(result)

            connection.Close()
        End Using
    End Using

If you are just looking to see if the user exists, try the following code.

     Dim ConnectionString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ToString()
    Using connection = New SqlConnection(ConnectionString)

        Using command As New SqlCommand()
            Dim userId As String = "3"
            command.Connection = connection
            command.CommandText = "Select Count(UserId) FROM tblTable _
                                      where UserId= '" & userId & "'"

            connection.Open()
            Dim result = command.ExecuteScalar()

            Console.WriteLine(result)

            connection.Close()
        End Using
    End Using

This will tell you in the how many rows containing the userIds are in the table

if you want to see the UserID/Site Comination with counts that is a another story

     Dim ConnectionString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ToString()
    Using connection = New SqlConnection(ConnectionString)

        Using command As New SqlCommand()
            Dim userId As String = "3"
            command.Connection = connection
            command.CommandText = "Select UserID, Count(SiteId) FROM tblTable where _   
                          UserId= '" & userId & "'" & " Group By UserId"

            connection.Open()
            Dim dataReader = command.ExecuteReader()
            Do While dataReader.Read()
                Console.WriteLine( _
                    vbTab & "UserId: {0}" & vbTab & "SiteID Count: {1}", _
                 dataReader(0), dataReader(1))
            Loop
            connection.Close()
        End Using
    End Using

I think that should get you on the correct Track

I will do with the both of the last two examples I they will work well on different pages. — indofraiser, Jan 14 '14 at 14:12

score 0 · Answer 2 · answered Jan 14 '14 at 12:56

There is no problem with a where clause in the query:

select count(distinct userid)
from tblTable
where UserID= '" & Session.Item("UserID").ToString & "'";

It is possible that in some SQL engines, the additional parentheses around userid are not allowed.

In any case, this is not the best way to determine if a row exists, because it has to do a fair amount of processing. Instead, check the number of rows returned by this query (if the engine supports limit):

select 1
from tblTable
where UserID= '" & Session.Item("UserID").ToString & "'"
limit 1

or this (in SQL Server/Sybase):

select top 1 1
from tblTable
where UserID= '" & Session.Item("UserID").ToString & "'"
limit 1;

SQL Select Count Distinct with WHERE clause

2 Answers2