How can I count duplicate rows according to specific columns?

Question

I have a table like this:

// financial_supporter
+----+---------+--------+
| id | user_id | amount |
+----+---------+--------+
| 1  | 342     | 1000   |
| 2  | 234     | 6500   |
| 3  | 675     | 500    |
| 4  | 342     | 500    |
| 5  | 89      | 800    |
| 6  | 234     | 1500   |
| 7  | 342     | 1200   | 
+----+---------+--------+

I need to select all columns of table above plus one more column named "for_the_n_time". And it should be containing how many times the user has supported us.

So the expected result is:

// financial_supporter
+----+---------+--------+----------------+
| id | user_id | amount | for_the_n_time |
+----+---------+--------+----------------+
| 1  | 342     | 1000   | 3              | -- for the third time
| 2  | 234     | 6500   | 2              | -- for the second time
| 3  | 675     | 500    | 1              | -- for the first time
| 4  | 342     | 500    | 2              | -- for the second time
| 5  | 89      | 800    | 1              | -- for the first time
| 6  | 234     | 1500   | 1              | -- for the first time
| 7  | 342     | 1200   | 1              | -- for the first time
+----+---------+--------+----------------+

Here is my query which is incomplete. I guess I need a self-join, but I cannot implement totally.

SELECT fs.*, <I don't know> as for_the_n_time
FROM financial_supporter fs
INNER JOIN financial_supporter as fs2 ON <I don't know>
WHERE 1
ORDER BY id DESC

Any idea how can I do that?

Edited: Also how can I make it DESC order like this:

// financial_supporter
+----+---------+--------+----------------+
| id | user_id | amount | for_the_n_time |
+----+---------+--------+----------------+
| 7  | 342     | 1200   | 3              | -- for the third time
| 6  | 234     | 1500   | 2              | -- for the second time
| 5  | 89      | 800    | 1              | -- for the first time
| 4  | 342     | 500    | 2              | -- for the second time
| 3  | 675     | 500    | 1              | -- for the first time
| 2  | 234     | 6500   | 1              | -- for the first time
| 1  | 342     | 1000   | 1              | -- for the first time
+----+---------+--------+----------------+

Maybe this can helps you . [Link](https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) . It says MySql needs `sql_mode=only_full_group_by` — Harun KARATAŞ, Mar 05 '18 at 06:08
Possible duplicate of [Rank function in MySQL](https://stackoverflow.com/questions/3333665/rank-function-in-mysql) — Jacobm001, Mar 05 '18 at 06:14

Tim Biegeleisen · Accepted Answer · 2018-03-05T06:34:38.137

1

You may compute your generated column using a correlated subquery. I assume that the date of the record correlates with the id column, i.e. earlier contributions would have a lower id than later contributions.

SELECT *,
    (SELECT COUNT(*) FROM financial_supporter fs2
     WHERE fs1.user_id = fs2.user_id AND fs2.id <= fs1.id) for_the_n_time
FROM financial_supporter fs1
ORDER BY id DESC;

Demo

edited Mar 05 '18 at 06:34

answered Mar 05 '18 at 06:14

Tim Biegeleisen

502,043
27
286
360

Ok thank you. your answer is right and I marked is as expected. Just now I understand I want a DESC order. I updated my question. And when I write `order by id desc` the order will be right but `for_the_n_time` column is wring. Anyway, can you please update your answer? – Martin AJ Mar 05 '18 at 06:33
@MartinAJ I originally had done this, but your question had it backwards, leading me to think that maybe that was the way you wanted it. – Tim Biegeleisen Mar 05 '18 at 06:35

How can I count duplicate rows according to specific columns?

1 Answers1

Demo