select increment counter reset by user in mysql

Question

I read about create variable for increment in select in this post select increment counter in mysql

I need some addition to this query.. I need to reset the increment based on user_id.

Sample data :

id user_id  name
1  1        A        
2  2        B        
3  3        C        
4  1        D        
5  2        E
6  2        F
7  1        G
8  3        H

Expected result:

id user_id  name     increment
1  1        A        1
4  1        D        2
7  1        G        3
2  2        B        1
5  2        E        2
6  2        F        3
3  3        C        1
8  3        H        2

It's not stop only until 3 increments, if I have more row with user_id, it will continue the increment. How do I make query so the output look like that? Thanks!

I just answered, hope the my logic in calculating the `increment` is ok by you, else what is it? — cdaiga, Mar 19 '18 at 11:16

Lahiru Madusanka · Answer 1 · 2018-04-06T05:57:52.433

0

You do not need a variable to do that. simple COUNT(), and GROUP BY would do the trick.

SELECT user_id, name, COUNT(user_id) AS increment FROM your_table GROUP BY user_id;

But this will return a single value for one user.

This will pass all count.

SELECT user_id, name, (SELECT COUNT(t2.user_id) FROM your_table t2 WHERE t2.user_id=t1.user_id) AS increment FROM your_table t1;

I'm not a expert at larevel. This is a simple PHP code.

<?php
$db = new mysqli("localhost","root","","test");
$sql = $db->query("SELECT * FROM your_table ORDER BY user_id");
$name_array = array();
$array_counts = array();
?>

<table>
    <tr>
        <th>id</th>
        <th>user id</th>
        <th>name</th>
        <th>increment</th>
    </tr>
    <?php
    while ($row = $sql->fetch_assoc()){
        if (in_array($row['user_id'],$name_array)){
            $array_counts[$row['user_id']][0] = $array_counts[$row['user_id']][0]+1;
        } else {
            $array_counts[$row['user_id']][0] = 1;
        }
        array_push($name_array,$row['user_id']);
        ?>
    <tr>
        <td><?php echo $row['id']; ?></td>
        <td><?php echo $row['user_id']; ?></td>
        <td><?php echo $row['name']; ?></td>
        <td><?php echo $array_counts[$row['user_id']][0]; ?></td>
    </tr>
    <?php
    }
    ?>
</table>

edited Apr 06 '18 at 05:57

answered Mar 19 '18 at 10:28

Lahiru Madusanka

270
2
13

This doesn't work.. the output for `user_id` = 1 has `increment` = 2 in each row – Denny Rustandi Mar 19 '18 at 10:47
1st one or the 2nd one? 2nd one is passing all count that have in the database for each row. – Lahiru Madusanka Mar 19 '18 at 10:49
the second query – Denny Rustandi Mar 19 '18 at 10:52
if you wand to get data as you described it can be done easily using your server site language. if you using PHP i can help with the code. – Lahiru Madusanka Mar 19 '18 at 10:54
can u tell me? I'm using `laravel` for backend – Denny Rustandi Mar 19 '18 at 10:58
I edited the answer and added the php code. i'm not a larevel expert and i thought simple php code might help. – Lahiru Madusanka Apr 06 '18 at 05:59

score 0 · Answer 2 · answered Mar 19 '18 at 11:15

0

Try this:

SELECT A.*, CASE WHEN id=user_id THEN 1
                 WHEN id>user_id THEN 2
                 ELSE 0 END increment
FROM yourTable A
ORDER BY A.user_id;

See it run on SQL Fiddle.

answered Mar 19 '18 at 11:15

cdaiga

4,861
3
22
42

score 0 · Answer 3 · answered May 18 '22 at 14:48

I was looking for something similar and I may have worked it out (maybe it wasn't exactly what you were looking for) :

SET @increment := 0;
SET @user_id_mem := 0;

SELECT
  @increment := CASE WHEN tbl.user_id = @user_id_mem THEN (@increment+1) ELSE 1 END as increment,
  tbl.id,
  @user_id_mem := tbl.user_id,
  tbl.name
FROM YOURTABLE tbl
ORDER BY user_id;

select increment counter reset by user in mysql

3 Answers3