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

    Question Unanswered: Select Count (DISTINCT)

    I keep getting an error of "too few parameters" for this SQL statement:

    sql="SELECT COUNT(customer) AS qtysurvey FROM (SELECT DISTINCT email FROM datatbl) Where customer = 'Yes' OR customer = 'No'"

    I have tried this also:

    sql="SELECT COUNT(*) AS qtysurvey FROM (SELECT DISTINCT email FROM datatbl) Where customer = 'Yes' OR customer = 'No'"

    Any ideas?

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251

    Re: Select Count (DISTINCT)

    First of all, I think you are talking about MS SQL STORED PROCEDURE, right?


    your subquery does not include the field you use in WHERE ....

    If you use SQL server, what about to use COUNT(DISTINCT myValue)



    jiri

  3. #3
    Join Date
    Oct 2002
    Location
    Houston, TX
    Posts
    34

    Re: Select Count (DISTINCT)

    Originally posted by playernovis
    First of all, I think you are talking about MS SQL STORED PROCEDURE, right?


    your subquery does not include the field you use in WHERE ....

    If you use SQL server, what about to use COUNT(DISTINCT myValue)



    jiri

    Yes, I see what you mean about the customer field not being in the subquery. Im not sure what you mean by the response COUNT(DISTINCT myValue)?

    Basically, I want a way to count the customer field with either a yes or no answer (not empty) but only for distinct people (determined by the email field)

    Any ideas on how to write the query?

  4. #4
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    1. do you use MDB or MS SQL as backend? SQL has COUNT DIST fuction and you can do it really really easily.

    2. As I understand it correctly, you have a table with fields email and cutomer and you want to count YES and NO from field customer based on field EMAIL, right?


    email customer
    me@aa.com YES
    as@dd.com NO
    me@aa.com YES


    gives you
    YES 1
    NO 1


    is this correct assumption?

  5. #5
    Join Date
    Oct 2002
    Location
    Houston, TX
    Posts
    34
    Originally posted by playernovis
    1. do you use MDB or MS SQL as backend? SQL has COUNT DIST fuction and you can do it really really easily.

    2. As I understand it correctly, you have a table with fields email and cutomer and you want to count YES and NO from field customer based on field EMAIL, right?


    email customer
    me@aa.com YES
    as@dd.com NO
    me@aa.com YES


    gives you
    YES 1
    NO 1

    Im Using SQL.

    I just want a count of the field customer with something in it from distinct emails.

    There is a form with an email entry and a question of "Are you a customer? ()Yes ()No This question is not required. If they don't answer it the field is blank.

    I want to count the number of distinct people that answered the question.

    Thanks!
    is this correct assumption?

  6. #6
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    I don't have SQL server in front of me right now.... but this should work.


    SELECT Customer, Count (DISTINCT email) FROM datatbl
    GROUP BY Customer


    or


    SELECT Customer, Count (email) FROM (SELECT DISTINCT Cutomer, Email FROM datatbl) GROUP BY Customer




    notes:
    1. you should try to have GROUP BY or any other fields defined exactly, Yes/No/DidNotAnswer
    2. if you have an email two times with two different answers, that person will count two times



    jiri

  7. #7
    Join Date
    Oct 2002
    Location
    Houston, TX
    Posts
    34
    Originally posted by playernovis
    I don't have SQL server in front of me right now.... but this should work.


    SELECT Customer, Count (DISTINCT email) FROM datatbl
    GROUP BY Customer


    or


    SELECT Customer, Count (email) FROM (SELECT DISTINCT Cutomer, Email FROM datatbl) GROUP BY Customer




    notes:
    1. you should try to have GROUP BY or any other fields defined exactly, Yes/No/DidNotAnswer
    2. if you have an email two times with two different answers, that person will count two times



    jiri

    No that did not work but someone else on another forum came up with a solution that did.

    sql="SELECT COUNT(*) AS qtysurvey FROM (SELECT DISTINCT email FROM datatbl Where customer = 'Yes' OR customer = 'No')"

    It didn't look like it would work but it did!

    Thanks!

  8. #8
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    hmmm, this is really different task. Why did I send you a sample and ask you if it is what you want? ......

Posting Permissions

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