CODEWITHSUNDEEP
mysqlcountgroup-by
Vahid Najafi
Vahid Najafi

Reputation: 5263

mysql count(*) with group by get one by one , not the total sum

The main idea is this:

id  name  count_n
1   'a'   3
2   'a'   3
3   'a'   3
4   'b'   2
5   'b'   2

I want to get the count_n like this:

id  name  count_n
1   'a'   1
2   'a'   2
3   'a'   3
4   'b'   1
5   'b'   2

How can I do this?

Upvotes: 0

Views: 24

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72215

You can use variables for this:

SELECT id, name,
       @seq := IF(@n = name, @seq+1,
                  IF(@n := name, 1, 1)) AS count_n
FROM mytable
CROSS JOIN (SELECT @seq := 0, @n := '') AS v
ORDER BY id

Demo here

Upvotes: 1

Related Questions