If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > HAVING (COUNT(category) > 1) , not only 1 row

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
9th inning DBA
 
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 --
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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)
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,780
Code:
SSELECT user_name,
       category
FROM journal
WHERE category IN (SELECT category
                   FROM journal
                   GROUP BY category
                   HAVING COUNT(*) > 1)
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Dec 2005
Posts: 266
i'll try

thank you
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On