Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    Houston, TX
    Posts
    34

    Unanswered: Select Count (DISTINCT)

    I am having trouble with is SQL on a MS Access DB. I have seen other similar post here on this but was not able to solve the problem


    sql2="SELECT COUNT(DISTINCT email) AS qtypeople FROM datatbl"

    Set recordset2 = Conn.Execute(sql2)
    qtypeople = recordset2(0)
    Response.Write ("Total Unique Registered People = " & qtypeople)


    I get the following error when this runs:

    Syntax error (missing operator) in query expression 'COUNT(DISTINCT email)'.

    I have also tried:

    sql2="SELECT DISTINCT COUNT(email) AS qtypeople FROM datatbl"
    Which will return a number but it is the total records rather than the distinct.

  2. #2
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84

    Re: Select Count (DISTINCT)

    Originally posted by rob7765
    I am having trouble with is SQL on a MS Access DB. I have seen other similar post here on this but was not able to solve the problem


    sql2="SELECT COUNT(DISTINCT email) AS qtypeople FROM datatbl"

    Set recordset2 = Conn.Execute(sql2)
    qtypeople = recordset2(0)
    Response.Write ("Total Unique Registered People = " & qtypeople)


    I get the following error when this runs:

    Syntax error (missing operator) in query expression 'COUNT(DISTINCT email)'.

    I have also tried:

    sql2="SELECT DISTINCT COUNT(email) AS qtypeople FROM datatbl"
    Which will return a number but it is the total records rather than the distinct.
    As long as you're opening a recordset, why don't you try changing your sql2 string to:
    "SELECT DISTINCT email FROM datatbl"

    Go ahead and open the recordset using your existing syntax and then get the number of distinct records from the recordset's recordcount property:
    qtypeople = recordset2.recordcount

    Note that if you're using DAO a recordset you need to access each of the records before the recordset property will tell you how many records there are. That's because the property tells you how many records have been accessed, not how many total records there are. If you're using ADO, the recordset property telss you the total number of distinct records witout accessing any of the records. If you have an option, I would recommend using ADO for this.
    Regards,
    Terry

  3. #3
    Join Date
    Oct 2002
    Location
    Houston, TX
    Posts
    34
    It gave me a -1 when I did this.

    Here is what the whole thing looks like:

    <%@Language=VBScript%>
    <%

    sql="SELECT SUM(registered) AS qtyreg FROM phototbl"
    sql2="SELECT DISTINCT email FROM datatbl"
    sql3="SELECT COUNT(Code2) AS qtyphotos FROM phototbl"


    %>
    <%

    Set conn=Server.CreateObject("ADODB.Connection")
    Set recordset = Server.CreateObject("ADODB.Recordset")
    Set recordset2 = Server.CreateObject("ADODB.Recordset")

    conn.Open "DSN=optinb.nasaDSN"

    Set recordset = Conn.Execute(sql)
    qtyreg = recordset(0)
    Response.Write ("Total Registered Photos = " & qtyreg & "<br>")

    Set recordset2 = Conn.Execute(sql2)
    qtypeople = recordset2.recordcount
    Response.Write ("Total Unique Registered People = " & qtypeople & "<br>")

    Set recordset = Conn.Execute(sql3)
    qtyphotos = recordset(0)
    Response.Write ("Total Uploaded Photos = " & qtyphotos & "<br>")

    'close the connection and the recordset
    recordset.Close
    recordset2.Close
    Set recordset=Nothing
    Set recordset2=Nothing
    conn.Close
    Set conn=Nothing
    %>


    Here is the out put this gives:

    Total Registered Photos = 251
    Total Unique Registered People = -1
    Total Uploaded Photos = 933

  4. #4
    Join Date
    Nov 2001
    Posts
    336
    Try query:

    SELECT Count(email) AS CountOfPeople
    FROM (SELECT email FROM qtypeople Group By email);

    HTH, Igor

  5. #5
    Join Date
    Oct 2002
    Location
    Houston, TX
    Posts
    34
    Thanks, that worked... I just found the same info at (of all places) microsoft.com.

    Microsoft Knowledge Base Article - Q306250






    Originally posted by IGelin
    Try query:

    SELECT Count(email) AS CountOfPeople
    FROM (SELECT email FROM qtypeople Group By email);

    HTH, Igor

Posting Permissions

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