Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Oct 2010
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Query Help: Returning distinct values from Count subquery

    I've been stuck for a while now trying to get this moderate query work. I may be going about this wrong as well. Here's the setup:

    I have a [Notes] table that contains a nonunique (Number) column and a nonunique (Result) column. I'm looking to create a SELECT statement that will display each distinct (Number) value where the count of the {(Number), (Result)} tuple where Result = 'NA' is > 25.

    For the actual purpose of the query, we have an autodialer that calls a number and returns a code depending on the results of the call. We want to ignore numbers that have had an 'NA'(no answer) code returned more than 25 times.

    My basic attempts so far have been similar to:

    Code:
    SELECT DISTINCT n1.Number FROM Notes n1
    WHERE
    (SELECT COUNT(*) FROM Notes n2
        WHERE n1.Number = n2.Number and n1.Result = 'NA') > 25
    I know this isn't correct, but if it's even the basic right approach, I'm not sure how to relate the DISTINCT n1.Number from the initial select to the Number used in the subquery COUNT.

    Any ideas or suggestions would be greatly appreciated. I haven't had to deal much with queries in over half a decade, so I'm a little rusty.

    -----

    Not sure how to delete this post, but I've found out my own answer. To anyone that was interested:

    Code:
    SELECT Number
    FROM Notes 
    WHERE Result = 'NA'
    GROUP BY Number
    HAVING COUNT(Result) > 25
    Last edited by jfelber; 10-22-2010 at 07:09 AM. Reason: Found solution

  • #2
    New to the CF scene
    Join Date
    May 2006
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello,
    I believe you have already solved your problem but here is my solution

    Code:
    SELECT Number, Result
    FROM Notes 
    WHERE Result = 'NA'
    Group By Number, Result
    HAVING COUNT(*) > 25
    I hope it helps


  •  

    Tags for this Thread

    Posting Permissions

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