A little help is needed if you’d be so kind.
I have a MySQL table entitled hits which records unique and non-unique visits to pages on my site. A non-unique visit is classed as a repeated visit by the same IP address within a 24-hour time period.
For the purposes of statistics, I want to analyse visits across my website. I can do that using unique and non-unique totals but not BOTH.
Here’s part of the table:
What I want to do is add together the values in the hitcount column regardless of whether isunique is 1 or 0 and then sort the results in descending order.
So, for the pageid 0050, I want to get 172 (95+77).
This is what I’ve tried:
$hitcountquery = "SELECT hitcount,pageid FROM hits WHERE pageid NOT LIKE '999%' GROUP BY pageid ORDER BY hitcount DESC";
The reason I’ve got that NOT LIKE in the query is that I want to ignore admin pages from the results.
Any idea what MySQL command I would need to add the totals in the hitcount column for each pageid and then sort them in descending order? I’ve had limited success but the titles are repeated in the table. I need them grouped so that each pageid appears only once in the results.
Thanks so very much in advance. You could just save my sanity!
>Solution :
After reading your details I think what you really want is the sum of your column, if that’s the case you can use the SUM() function for your hitcount column like this:
$hitcountquery = "SELECT SUM(hitcount),pageid FROM hits WHERE pageid NOT LIKE '999%' GROUP BY pageid ORDER BY hitcount DESC";
hops this help.