CODEWITHSUNDEEP
mysqlsql
Speedwheelftw
Speedwheelftw

Reputation: 393

SQL: count progressively unique visits from table

I have this table (visits):

id  |       fb_id    |   flipbook    | 
----     ----------      ---------     
1         1123           november2014        
2         1123           november2014      
3         1127           november2014       
4         1124           november2014    
5         1126           november2014            
6         1123           december2014
7         1124           december2014
8         1125           december2014
9         1123           january2015  
10         1124           january2015
11        1125           january2015
12         1123           february2015
13         1125           february2015
14        1124           february2015
15        1127           february2015
16        1129           march2015
17        1123           march2015
18        1123           march2015
19        1124           march2015
20        1125           march2015
21        1126           march2015
22        1128           march2015

We have 5 flipbooks in total, and after the query I want to get the following results:

5: 10 (because we have 2 users that visited all the flipbooks = 10 visits)
4: 4 (only 1 user visited 4 flipbooks = 4 visits)
3: 0
2: 4 (2 users visited  2 flipbook = 4 visits )
1: 2 (2 users visited only 1 flipbook = 2 visits)

I don't have any query in mind to post here to achive this result, any ideas?

Upvotes: 0

Views: 54

Answers (2)

Shadow
Shadow

Reputation: 34232

I would create a subquery that counts the distinct flipbooks by fb_id. The outer query would count the inner counts and multiply them as well. You can use the concat() function if you want to get the results in a single field as t.visited:no_of_visits format without any additional subquery.

select t.visited, count(t.visited) * t.visited as no_of_visits
from
    (select fb_id, count(distinct flipbook) as visited
     from vistits group by fb_id) t
group by t.visited

Upvotes: 3

sagi
sagi

Reputation: 40481

SELECT s.cnt ||' : '|| s.cnt*s.cnt2 from (
SELECT p.cnt,count(*) cnt2 from (
SELECT t.fb_ind,count(distinct t.flipbook) as cnt from yourTable t
group by t.fb_ind) p
group by p.cnt) s

This is untested , so maybe there will need to be some syntax anjustments, but it should work.

First - distinct your table, then - group by for getting Num of flip visited, then - group by to see how many did the same flips, and then concate it and make your calc.

Upvotes: 1

Related Questions