CODEWITHSUNDEEP
phpmysql
Brad
Brad

Reputation: 417

Using SQL to COUNT the most recent rows only

I'm trying to build an analytic page, and now I've encountered a problem; users visit pages based on their session, if that session expires then they are provided with a new one.

I'm trying to determine a way to COUNT the number of users where their last session was on a certain page using this query:

SELECT DISTINCT (SELECT MAX(session) FROM analytics b WHERE a.session=b.session) as session,(SELECT MAX(DISTINCT location) FROM analytics c WHERE c.session=a.session) as locale FROM analytics a

That query will return results as follows:

session |           location            
------------------------------------------
1       | http://random-page.io/index.html -- Same session, first entry          
1       | http://random-page.io/index.html -- Same session, second entry        
1       | http://random-page.io/index.html -- Same session, last entry   <- What We're trying to Count        
2       | http://random-page.io/index.html -- Same session, first entry        
2       | http://random-page.io/index.html -- Same session, last entry <- What We're trying to Count          
3       | http://random-page.io/index.html -- One session, presumably serves as last and first entry.. but last is what matters  <- What We're trying to Count
4       | http://random-page.io/drafts.html -- One Session  <- What We're trying to Count
5       | http://random-page.io/elements.html -- One session  <- What We're trying to Count

What I want to be able to do is be able to count the rows where their session ends only, and to truncate all duplicate results (by using GROUP BY and COUNT) so that my query returns the following:

count   |           location            
------------------------------------------
3       | http://random-page.io/index.html -- The count is 3 and not 5 because there are 3 sessions in which the LAST entry for their assigned session is http://...index.html
1       | http://random-page.io/drafts.html -- You catch my drift
1       | http://random-page.io/elements.html  -- Coolio <3

Is this at all possible?

Upvotes: 2

Views: 556

Answers (3)

Abhishekh Gupta
Abhishekh Gupta

Reputation: 6236

You can give this a try:

SELECT
  COUNT(*) AS count,
  a.lastEntry AS location
FROM (
        SELECT 
          session, 
          SUBSTRING_INDEX(GROUP_CONCAT(location), ',', -1) AS lastEntry
        FROM analytics 
        GROUP BY session
    ) AS a
GROUP BY a.lastEntry;

Here is the sqlfiddle.

Upvotes: 1

arkascha
arkascha

Reputation: 42959

Looks like you require a subselect for this...: 

SELECT count(session) AS count, location FROM ( 
    SELECT session, location from requests GROUP BY session
) AS I 
GROUP BY location;

Here is a sql fiddle to play around with: http://sqlfiddle.com/#!9/41f15/20

Upvotes: 1

Abdelrhman Adel
Abdelrhman Adel

Reputation: 1187

try this (well it's ugly but I can't figure out another way to do it)

select
    grouped_analytics.location, count(grouped_analytics.session)
from
    (select 
        analytics.session,analytics.location 
    from 
        analytics 
    group by 
        analytics.session, analytics.location
    ) as grouped_analytics
group by
    grouped_analytics.location

Upvotes: 1

Related Questions