Developer forum

Forum » Development » News statistics

News statistics

E-mail notifications

Reply
Posted on 14/10/2009 12:49:23
Hi,

Since the statistics module makes it very hard to keep track of the most read news, I need to develop a report using the database publishing module, but the problem is the SQL criteria, can anyone help me?

I have tried searching in the databases, but I can't find it in the news table nor in any statistics table.

Best Regards,
Nuno Aguiar

Replies

 
Nicolai Høeg Pedersen
Posted on 16/10/2009 11:41:26
Reply

The information can be found in Statv2Object table... 
 

SQL listing the most viewed News IDs in the past month - this is for Access - maybe it is a little different on SQL-Server: 
 

SELECT Count(Statv2Object.Statv2ObjectSessionID) AS CountOfStatv2ObjectSessionID, Statv2Object.Statv2ObjectElement as newsID FROM Statv2Object WHERE (((Statv2Object.Statv2ObjectTimestamp)>Now()-30)) GROUP BY Statv2Object.Statv2ObjectElement, Statv2Object.Statv2ObjectType HAVING (((Statv2Object.Statv2ObjectType)="news")) ORDER BY Count(Statv2Object.Statv2ObjectSessionID) DESC 


If it is a SQL-Server based solution, you can innerjoin this to the News table (News.NewsID on Statv2Object.Statv2ObjectElement) to get the name of the news in the list. 
 

Furthermore you can join Statv2Object.Statv2ObjectSessionID on Statv2Session.Statv2SessionSessionID to get information from rest of the statistics information for that visit. 


Hope this helps.

 
Posted on 16/10/2009 12:05:55
Reply
Hi Nicolai,

yes it helps quite a lot. Thank you.

Best Regards,
Nuno

 

You must be logged in to post in the forum