CODEWITHSUNDEEP
mysql
Nikolay
Nikolay

Reputation: 131

Mysql numbered rows in group

I have table

    users|visits
    --------------
    user1|visit1
    user1|visit2
    user1|visit3
    user2|visit1
    user2|visit2

I want to get numbered rows in any group. How i can do this?

users|visits|visit number
-----------------------
user1|visit1|1
user1|visit2|2
user1|visit3|3
user2|visit1|1
user2|visit2|2

Upvotes: 0

Views: 41

Answers (2)

AnkiiG
AnkiiG

Reputation: 3488

Try as below :

SELECT 
users, visits, count(*)  as `visit number`
FROM tableName group by visits,users

Upvotes: 1

KuKeC
KuKeC

Reputation: 4610

From what you have provide about your problem i can imagine that you have records something like this

users|visits
------------
user1|visit1
user1|visit2
user1|visit1
user1|visit3

And you want to get 

users|visits|visit number
-------------------------
user1|visit1|2
user1|visit2|1
user1|visit3|1

To do that use query like this

SELECT 
DISTINCT a.users, 
a.visits, 
(select count(*) from tableName b where b.users = a.users and b.visits = a.visits) as `visit number`
FROM tableName  a

Upvotes: 0

Related Questions