kinda noobie question

[Guest_imported]

Hi, I have a DB that stores hits on pictures for a photo album. it stores IP, photoname, and datetime.

I want to make a SQL query to get back two columns, one the name each distinct photo, the other the number of tupples(or hits) that photo has. then I can calculate which photos are in the top 50% of hits and choose a pic of the week automatically.

I'm still pretty noobish with SQL and I can't get this query. anyone have a solution?

 

[PHV]

A starting point:
SELECT photoname, COUNT(*) Hits
FROM yourTable
GROUP BY photoname

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[baltman]

You might also be interested in TOP and ORDER BY.

SELECT TOP 100 photoname, sum(Hits) as Hits
FROM yourTable
GROUP BY photoname
ORDER BY sum(Hits) DESC

 

[Guest_imported]

Thanks I got everything worked out now, I did something similar to TOP, I said DESC LIMIT 100 or whatever.

thanks guys