***SOLVED Counting duplicates in a MySql query

[switchdoc]

Hello all,

  I am hoping you can help me with what I am sure is a very simple issue.

I need to keep track of the date and number of times certain links are clicked. To do that I've written a little script that pushes in the linkID and the date it was visited to a mysql db when that page is visited.

The DB looks something like this: 

[code]
     LinkID  clickdate 
      604   2006-11-08
      111   2006-11-08
      111   2006-11-08
      111   2006-11-08
      229   2006-11-09
      662   2006-11-09
      183   2006-11-25
      229   2006-11-09
      183   2006-11-09

[/code]

You'll note that LinkID 111 got hit 3 times on the same day. LinkID 183 got hit twice.

When I output this I want to show

LinkID: 111 Hits: 3
LinkID: 183 Hits: 2
LinkID: 229 Hits: 1

etc...

I can't figure out the correct way to count this out of the query.

Doing a select all and running it through a while loop lets me pull everything out, but somewhere there in the middle I have to add up the duplicates.

Any thoughts appreciated!!

-Switch

[btherl]

[code]SELECT LinkID, clickdate, sum(1) as Hits
FROM table
GROUP BY LinkID, clickdate[/code]

There you go :)

[Nicklas]

Try something like this

[code=php:0]SELECT *, COUNT(LinkID) AS total FROM your_table GROUP BY LinkID[/code]

[btherl]

Oops.. you want them grouped by LinkID only right?  Ignoring the date?

[code]SELECT LinkID, sum(1) as Hits
FROM table
GROUP BY LinkID[/code]

"Select *" will not work because it selects clickdate without aggregating it (in theory anyway.. I have not tried this in mysql)

[switchdoc]

Thanks for the fast replies!

I don't suppose anyone can show me how to pull this out on the other end, I am not quite sure how to echo out the clickdate (sum).

Appologies in advanced and thanks!

-Switch

[Nicklas]

[quote]"Select *" will not work because it selects clickdate without aggregating it (in theory anyway.. I have not tried this in mysql)[/quote]

works for me...

[btherl]

Switchdoc, do you want seperate lines for each LinkID/clickdate combination (day-by-day stats), or just a seperate line for each LinkID (summary)?  From your first post I thought you wanted to ignore clickdate, but now I am unsure..

Nicklas, what output does it produce?  The query doesn't specify which value clickdate should take after grouping..

[switchdoc]

Well,

I just went back and looked and I think I screwed you all up.

The scoop is I need ALL hits for each link in a date range

So if the user selects 11/3 - 11/21

Each time the link appears in that date range, I need to count it so I can say

LinkID 111 Hits 3
LinkID 237 Hits 56

Sorry for the confusion.

-Switch 

[Nicklas]

[quote]Nicklas, what output does it produce?[/quote]

Im sorry, I forgot to add [i]clickdate[/i] in my query when I posted it,

my query looks like this
[code=php:0]SELECT *, COUNT(LinkID) AS total FROM your_table GROUP BY LinkID, clickdate[/code]


The data in my database looks like this:

[code]LinkID  clickdate
604  2006-11-08
111 2006-11-08
111 2006-11-08
111 2006-11-08
229 2006-11-09
662 2006-11-09
183 2006-11-25
229 2006-11-09
183 2006-11-09[/code]

After the query, it looks like this

[code]LinkID  clickdate  total
111 2006-11-08 3
183 2006-11-09 1
183 2006-11-25 1
229 2006-11-09 2
604 2006-11-08 1
662 2006-11-09 1[/code]

[switchdoc]

Hey all,

Thanks for all the replies! The answer just came via an email to a buddy I'd sent out earlier. Here is what he ended up having me do so I could get the hit count and use the page name (from another table) rather than just ID:



[code]
$query = "select count(t1.linkid),t2.pagename from page_counter as t1 inner join pages as t2 on t2.linkid=t1.linkid where t1.clickdate>='$sd' and t1.clickdate<='$ed' group by t1.linkid";
$result = mysql_query($query) OR DIE ("there was an error" .mysql_error());
while ($line = mysql_fetch_array($result,MYSQL_NUM)){list($count,$name) = $line;
[/code]

Then I can echo out the page name ($name) and the hits($count) into a table using the while loop.

Thanks again for all your replies!

-Switch