#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
    19
    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
    19
    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
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2014
    Posts
    3
    Rep Power
    0

    Found the solution


    Originally Posted by RyanP
    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?
    The answer is simple. you don't use WHERE, you use HAVING

    so:

    SELECT COUNT(foo) AS bar
    FROM tbl
    GROUP BY foo
    HAVING COUNT(foo) > 1
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,441
    Rep Power
    1688
    That was asked FOURTEEN years ago ...!
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    Originally Posted by SimonJM
    That was asked FOURTEEN years ago ...!
    yeah, but...

    when someone is wrong on the internet THEY MUST BE CORRECTED
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2014
    Posts
    3
    Rep Power
    0
    Originally Posted by r937
    yeah, but...

    when someone is wrong on the internet THEY MUST BE CORRECTED
    No, i answered because i needed the solution and the first result i found was this feed. If others need the solution, they will come here and the answer thus is here.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2014
    Posts
    3
    Rep Power
    0
    no, i responded because when i needed the answer to this, the first site i visited was this feed. now that the answer is here, people will find it when they come here.
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    Originally Posted by MichelleKIBA
    now that the answer is here, people will find it when they come here.
    there's only one problem -- the answer you posted is crap

    think about it -- your "solution" returns a column of counts without any indication of what those counts are counting

    what possible use could that be?

    if you want a hint as to where the real solution lies, go back and read post #3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,930
    Rep Power
    1225
    I wouldn't say the answer is "crap". I'd simply say it's incomplete. It's just missing the 1 field needed to identify the what's being counted.
    Code:
    mysql> select map, count(map) as count from records group by map having count(map) > 20;
    +--------+-------+
    | map    | count |
    +--------+-------+
    | map107 |    21 |
    | map155 |    25 |
    | map158 |    22 |
    | map192 |    26 |
    | map230 |    25 |
    | map256 |    24 |
    | map273 |    22 |
    | map59  |    21 |
    | map68  |    22 |
    | map71  |    23 |
    +--------+-------+
    10 rows in set (0.08 sec)
    Last edited by FishMonger; June 2nd, 2014 at 10:07 AM.

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