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

    Unanswered: 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,040
    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,912
    Provided Answers: 7
    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

  8. #8
    Join Date
    Feb 2016
    Posts
    1

    help me...

    Click image for larger version. 

Name:	query.png 
Views:	4 
Size:	22.1 KB 
ID:	16786

    can that be done??

    wud like to retrive the index values...

  9. #9
    Join Date
    Jan 2013
    Posts
    353
    Provided Answers: 1

    Is this what you meant?

    Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements.

    We need to know the data types, keys (key are no optional!) and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

    You should follow ISO-8601 rules for displaying temporal data. Read this cartoon to see how silly you are: (https://xkcd.com/1179/). Why did you fail to use the week date format?

    For a quick education on RDBMS and basic data modeling, read this eight part series: http://www.sqlservercentral.com/stairway/72899/

    What will you did post it makes no sense. There is no such thing as a "category"; it has to be a particular kind of category. Likewise, we need a "<particular kind>_user" in a valid data model.

    Based on nothing you posted [please do not do this again] I will guess you have some kind of users in some kind of nameless journal that may or may not fall into one or more nameless categories. That you want to find the users that are in more than one category.

    SELECT user_id
    FROM Somethign _journal
    GROUP BY user_id
    HAVING (COUNT (DISTINCT foobar_category) > 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
  •