George You need the HAVING clause to filter that way (and remember you can use COUNT(*) to get a count):
>SELECT GENRE, COUNT(*) FROM BOOK_TITLES GROUP BY GENRE; Genre..... COUNT ( * ) BIOGRAPHY 2 BUSINESS 8 CLASSIC 31 CRIME 1 DRAMA 15 FANTASY 68 FICTION 97 HISTORY 9 HUMOUR 17 LANGUAGE 2 01 SELECT GENRE, COUNT(*) FROM BOOK_TITLES GROUP BY GENRE HAVING COUNT(*) > 5 SQL+ Genre..... COUNT ( * ) BUSINESS 8 CLASSIC 31 DRAMA 15 FANTASY 68 FICTION 97 HISTORY 9 HUMOUR 17 7 records listed. If you want to sort as well, it's easier to use a column alias: 01 SELECT GENRE, COUNT(*) AS BCT FROM BOOK_TITLES GROUP BY GENRE HAVING BCT > 5 ORDER BY BCT; Genre..... BCT....... BUSINESS 8 HISTORY 9 DRAMA 15 HUMOUR 17 CLASSIC 31 FANTASY 68 FICTION 97 Brian -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of George Gallen Sent: 15 April 2011 14:22 To: U2 Users List Subject: [U2] Can this be done with SQL? I have a DICT item CNT setup as an I desc as (1=1) We use it like: SELECT FILENAME BY FIELD BREAK.ON FIELD TOTAL CNT DET.SUP Which works great for giving counts of FIELD - but you can't sort by it, or select off it. I tried: SELECT FIELD,COUNT(CNT) FROM FILENAME GROUP BY FIELD; Which also does the same, hoping, I could add a WHERE COUNT(CNT) > 1 but it won't allow you to add that clause on with out an error: What I want to know is which FIELD's in FILENAME have a TOTALCNT > 1 Any ideas? (Either using TCL or SQL) UV 10 Thanks George George Gallen Senior Programmer/Analyst Accounting/Data Division, EDI Administrator [email protected] ph:856.848.9005 Ext 220 The Wyanoke Group http://www.wyanokegroup.com _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
