Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    HAVING (COUNT(category) > 1) , not only 1 row

    i am using this code :

    SELECT MAX(user) AS lastuser, category
    FROM journal
    GROUP BY category
    HAVING (COUNT(category) > 1)

    it works but returns 1 line by category >1

    i need all the user (all the rows) HAVING (COUNT(category) > 1) , not only 1

    if 1 category has only 1 user i must not keep it


    i am not shure to be clear :-)

    thank you for helping

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,039
    If you need all the users fulfilling the requirements, why are you using the "max" argument. By its nature, the max (maximum) will return the largest value. Get rid of max, add user to your group by and try again.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    you mean

    SELECT user, category
    FROM journal
    GROUP BY user,category
    HAVING (COUNT(category) > 1)

    i dont get it in that way

    I dont need the lines : A|B where count(B) = 1
    i need only the lines : A|B. C|B, D|B count(B) > 1

    with my first code I get D|B (the last one)
    Last edited by anselme; 09-25-06 at 08:25.

  4. #4
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    I don't think that your query will work it would return
    where a user had the same category more than once
    not differeent categories for the same user.

    SELECT user
    FROM journal
    GROUP BY user
    HAVING (COUNT(category) > 1)

  5. #5
    Join Date
    Dec 2005
    Posts
    266
    i dont get it in that way
    i need for exemple 3 lines

    john tennis
    pierre tennis
    paul tennis


    3 lines if count(category) > 1

  6. #6
    Join Date
    Nov 2003
    Posts
    2,814
    Code:
    SSELECT user_name,
           category
    FROM journal
    WHERE category IN (SELECT category
                       FROM journal
                       GROUP BY category
                       HAVING COUNT(*) > 1)

  7. #7
    Join Date
    Dec 2005
    Posts
    266
    i'll try

    thank you

Posting Permissions

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