group by
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: group by

  1. #1
    Join Date
    Feb 2000
    Posts
    24
    I'm trying to grab duplicates from a table (answered in a previous post..) but I'm having trouble getting another column out of the table:
    (segments [segmentid, user_id, typeid])

    SELECT b.user_id, count(b.user_id) from segments b
    WHERE b.typeid = 100
    group by b.user_id
    having count(b.user_id) > 1

    That basically grabs all the duplicate user_id's of ppl with a typeid of 100. But I want to grab the unique identifier[segmentid] so I can delete them from the table. But everytime I add that field, i get a 'not a group by function' error.. and I dont want to group the segmentid because it wont grab the duplicates.

    SELECT b.segmentid, b.user_id, count(b.user_id) from segments b
    WHERE b.typeid = 100
    group by b.user_id
    having count(b.user_id) > 1

    Any help is appreciated. BTW: I LOVE THIS FORUM!!



  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Try the following statement:

    SELECT b.segmentid, b.user_id, count(b.user_id) from segments b
    WHERE b.typeid = 100
    group by b.segmentid, b.user_id
    having count(b.user_id) > 1

  3. #3
    Join Date
    May 2000
    Location
    Richardson, Texas, USA
    Posts
    39
    Try this:

    select s.userid, s.segmentid
    from segments s,
    (SELECT user_id, count(user_id) from segments
    WHERE typeid = 100
    group by user_id
    having count(user_id) > 1) b
    where s.typeid = 100 and s.userid=b.userid;

    I could not test this query but I think there is a good probability that this will give you the output that you are looking for.

    Thanks.

    Syed

  4. #4
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    this should work

    select *
    from table_1
    where (col_1, col_2, col_3) in
    (select col_1, col_2, col_3
    from table_1
    group by col_1, col_2, col_3
    having count(*) > 1);


    You don't need the count() in the select statment.

Posting Permissions

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



Click Here to Expand Forum to Full Width