#1
  1. No Profile Picture
    RyanP
    Guest
    Devshed Newbie (0 - 499 posts)
    i would like to count a column and only display records where the count is greater than 1

    i tried something like:

    SELECT COUNT(foo) AS bar
    FROM tbl
    WHERE bar > 1

    but that didn't work

    any ideas?
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    18
    I don't get what you are saying. count() returns a count of records. you can't use it to reference a record since it's not related to any one record.
  4. #3
  5. No Profile Picture
    RyanP
    Guest
    Devshed Newbie (0 - 499 posts)
    ****, i don't get what i was saying either

    i should have said that i'm trying to group records by how many duplicates there are and i only want to see records that have more than one occurance

    so i tried to use something like:

    SELECT Name, COUNT(Name) AS foo
    FROM tbl
    WHERE foo > 1
    GROUP BY Name

    but that didn't work, i assume that you just can't use aggregates in WHEREs

    any known workaround?

    hope that clears it up, sorry for the confusion
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    18
    You'd have to use the count(*) in the where and I'm not even sure if that would work:

    select name from tbl where count(*)>1 group by name;

    give it a try. If you have a unique identifier you could join the table to itself:

    select t1.name,t1.id,t2id from tbl t1,tbl t2 where t1.name=t2.name and t1.id!=t2.id;

  8. #5
  9. No Profile Picture
    RyanP
    Guest
    Devshed Newbie (0 - 499 posts)
    i get a "ERROR 1111: Invalid use of group function" whenever i try to use an aggregate function in a WHERE

    but joining the table on itself worked great, thanks for the idea

Similar Threads

  1. SPs in Access 2000 (SQL) / Crosstab problem / returning dataset
    By michaeldallas in forum MS SQL Development
    Replies: 1
    Last Post: September 1st, 2005, 01:41 PM
  2. sql column join?
    By dbosz in forum MySQL Help
    Replies: 4
    Last Post: January 30th, 2004, 09:34 AM
  3. Replies: 1
    Last Post: January 13th, 2004, 06:14 PM
  4. error 1055 - column alias, group by, having count
    By smossbarg in forum MySQL Help
    Replies: 0
    Last Post: December 8th, 2003, 05:59 PM
  5. sql UPDATE table
    By imustgo in forum ASP Programming
    Replies: 3
    Last Post: November 11th, 2003, 09:28 AM

IMN logo majestic logo threadwatch logo seochat tools logo