CODEWITHSUNDEEP
mysql
Michael Jones
Michael Jones

Reputation: 115

How to output the number of times data occurs using SQL?

Given the following data:

visit_id
   1
   1
   1
   2
   3
   3
   4
   5

is it possible using only sql (mysql's dialect actually, and no loops in another programming language) to output:

total visits    number of visitor ids
       1             3
       2             1
       3             1

i.e. to break down the data into the number of times they occur? So in the example above, there are 3 visit ids that only occur once (2,4,5), one visit id that occurs twice (3), and one that occurs three times (1).

thanks

Upvotes: 0

Views: 1470

Answers (2)

Mark Baker
Mark Baker

Reputation: 212462

Building on František's answer

select acc.visitCount as total_visits,
       count(acc.visitCount) as number_of_visitor_ids
  from (
select visit_id, 
       count(visit_id) as visitCount
  from visits 
 group by visit_id
) acc
group by acc.visitCount

Upvotes: 3

František Žiačik
František Žiačik

Reputation: 7614

Of course, it's called grouping.

select visit_id, count(visit_id) from visits group by visit_id

Upvotes: 4

Related Questions