CODEWITHSUNDEEP
mysqlsqlincrement
Alberto Moro
Alberto Moro

Reputation: 1013

MySQL: Increment column by field

I am trying to create some sort of list of fruits. I want create a temporary column increment_value that takes the count of how much fruit has been inserted by type.

I have this fruits table:

+----------+------------+
| fruit_id | fruit_name | 
+----------+------------+
| 5        | Apple      | 
| 8        | Banana     | 
| 8        | Banana     | 
| 5        | Apple      | 
| 5        | Apple      | 
+----------+------------+

Desired result:

+----------+------------+-----------------+
| fruit_id | fruit_name | increment_value |
+----------+------------+-----------------+
| 5        | Apple      | 1               |
| 8        | Banana     | 1               |
| 8        | Banana     | 2               |
| 5        | Apple      | 2               |
| 5        | Apple      | 3               |
+----------+------------+-----------------+

I tried with dummy table but failed: DEMO

How do I get the table I want?

Upvotes: 2

Views: 103

Answers (3)

Alberto Moro
Alberto Moro

Reputation: 1013

Thanks to @Gordon's reply. I taking inspiration from this post. I wrote this for MySQL 5.7.

SELECT  
    fruits.*,
    @row_num :=IF(@prev_value= fruit_id, @row_num+1 , 1)AS RowNumber,
    @prev_value := fruit_id
FROM fruits
CROSS JOIN (SELECT @row_num :=1,  @prev_value :=0) vars
ORDER BY fruit_id

Thank you all for your help!

Upvotes: 0

Mike Araya
Mike Araya

Reputation: 164

you can simply do a group by query like:

SELECT fruit_id, fruit_name, SUM(fruit_id) as increment_value
FROM fruits
GROUP BY fruit_id;

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use row_number():

select f.*,
       row_number() over (partition by fruit_name order by fruit_name) as seqnum
from fruits f;

Note: SQL tables and result sets represent unordered sets, so the ordering is indeterminate. You can adjust the ordering by changing the order by clause.

Upvotes: 3

Related Questions