MySQL problem

selecting distict items and the number of occurances

[GoodMoJo]

Hi

I have a db table that keeps track of all visitor information. Simplified its something like this:

+-----------+------+
¦ visit id ¦ Page ¦
+-----------+------+
¦  1  ¦ 1 ¦
¦  2  ¦ 1 ¦
¦  3  ¦ 2 ¦
¦  4  ¦ 1 ¦
¦  5  ¦ 3 ¦
¦  6  ¦ 3 ¦
¦  7  ¦ 3 ¦
+-----------+------+

What I'd really like to do is be able to select all the distinct page id (the 1's 2's and 3's int he example) and also select the count for each of the page id's so the example would give me something like this
->
1 : 3
2 : 1
3 : 3

Can anyone help me out with this?

-Thanks
GoodMoJo

[coopster]

SELECT DISTINCT Page, count(Page) from table_name GROUP BY Page; 

How MySQL Optimizes ORDER BY [mysql.com]

By default, MySQL sorts all

GROUP BY x,y[,...]

queries as if you specified

ORDER BY x,y[,...]

in the query as well. If you include the

ORDER BY

clause explicitly, MySQL optimizes it away without any speed penalty, though the sorting still occurs. If a query includes

GROUP BY

but you want to avoid the overhead of sorting the result, you can supress sorting by specifying

ORDER BY NULL

.

That said, I would probably include an

ORDER BY

clause just in case you ever port your code to a database that wouldn't explicity sort the result set in this manner. Therefore,

SELECT DISTINCT Page, count(Page) from table_name GROUP BY Page ORDER BY Page; 

is probably best practice. Regards -- coopster

[GoodMoJo]

Thank you!
I should have been able to figure out that one myself... ugh I had gotten really bogged down after reading different stuff mainly the manual [mysql.com...] I kept trying to do something like that example... anyways thanks again.

-GoodMoJo

[GoodMoJo]

okay wait real quickly i have another problem. I'm kinda a novice at using these group by commands. I seem to be getting an error

This is the actual query I was trying to use. I wanted to add a where clause that could select the page stats for in this case january.

SELECT DISTINCT Page, count(page) FROM stats.mojows_stats GROUP BY Page FROM `stamp` > '2004-01-01 00:00:00' AND `stamp` < '2004-2-01 00:00:00' ORDER BY 2 DESC LIMIT 10

-Thanks again

[mykel79]

Try:

SELECT DISTINCT Page, count(page) FROM stats.mojows_stats WHERE stamp > '2004-01-01 00:00:00' AND stamp < '2004-2-01 00:00:00' GROUP BY Page

[coopster]

mykel79 is correct. All clauses used must be given in exactly the order shown in the

SELECT

[mysql.com] syntax description. Therefore, a

GROUP BY

clause must come after any

WHERE

clause. And your

ORDER BY

clause must come after the

GROUP BY

clause. I see mykel79 forgot to append that:

SELECT DISTINCT Page, count(page) 
 FROM stats.mojows_stats 
 WHERE stamp > '2004-01-01 00:00:00' 
 AND stamp < '2004-2-01 00:00:00' 
 GROUP BY Page 
 ORDER BY 2 DESC 
 LIMIT 10 

[GoodMoJo]

Thanks, the help is greatly appreciated.