SQL experts help again please

[LynneSin]

I'm pulling a list of about 10k files but I want to narrow my search to only those files that show up 20 or more times on the list.

Didn't have counting in my class any help?

Any recommendations?

[ikri]

1. You need to group

If, for example, you wanted a list of company names that appear more than 20 times in a table you'd use:

Select d.CompanyName, count(*) as 'Total'
from exampletable as d
group by d.CompanyName
having count(*) > 20
order by count(*) desc

That should work.

[LynneSin]

3. the group command, damnit

forgot about that.

It'll have to wait until after lunch

Thanks!!!

[aePrime]

2. You can try something similar to:

SELECT files, COUNT(*) AS count FROM table GROUP BY file HAVING count >= 20

[no name no slogan]

4. databases are for wussies

If you can't store it in a plain-text flat file, it ain't worth storing.

[kick-ass-bob]

5. on the mainframe!

you forgot 'on the mainframe'

:D

[no name no slogan]

6. d'oh!

and from the command prompt too, dammit!

I like to run this little baby on the Solaris b0xen at work-- but only when I'm logged in as root:

rm -r /

You should see the look on the tech guys faces when they try to log into the machines. It's priceless!

[kick-ass-bob]

7. LOL!!

I love that game!

:rofl:

[LynneSin]

8. I work with a plain-text flat file

Built a database program around it

SO THERE!!!

I'm not a wussy

:woohoo:

[no name no slogan]

9. Betcha can't do that with punchcards, though

monitors are for the weak. Give me a cardreader and a keypunch ANY day.

My hand-punched Assembler routine can whip your pansy-assed SQL query's butt. Especially once I get my System 360 warmed up.

Kids these days...

[LynneSin]

10. Now you're just dating yourself

:P

[no name no slogan]

12. No, I actually have a girlfriend now

My days of self-dating are in the past, thankyouverymuch :silly:

[billyskank]

11. Not much info there

but you need something like

SELECT foo,bar, COUNT(*)
FROM baz,qux
WHERE quux
GROUP BY yada,yada,yada
HAVING COUNT (*) >= 20.