Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    33

    Unanswered: using count in a where or having clause

    Hi - this is a newbie question because im new to this.

    How do i do the following....

    select person, order from shopdb
    having count(distinct order) = '1'


    as you can imagine this isn;t working.

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by brucezepplin View Post
    Hi - this is a newbie question because im new to this.

    How do i do the following....

    select person, order from shopdb
    having count(distinct order) = '1'


    as you can imagine this isn;t working.
    Code:
    select person, order
    from shopdb as x
    join ( select person
            from shopdb
            group by person
            having count(distinct order) = 1 ) as y
        on x.person = y.person
    /Lennart
    --
    Lennart

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    First of all, count is numeric, and you used literal '1' so it cannot possibly work.

    Here is something a bit more intuitive IMO (if I understand your requirements):

    select
    person,
    order
    from shopdb a
    where 1 =
    (select count(*)
    from shopdb b
    were a.person = b.person);
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jul 2011
    Posts
    33
    thanks guys - both methods worked a treat.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?
    If this query worked, a good point is this query refers shopdb table once.

    Code:
    SELECT person
         , MAX(order) AS order
     FROM  shopdb
     GROUP BY
           person
     HAVING
           COUNT(*) = 1
        /* or
           COUNT(DISTINCT order) = 1
        */
    ;

Posting Permissions

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