CODEWITHSUNDEEP
mysql
NewUser
NewUser

Reputation: 13333

mySQL count duplicate entry based on date and other coulmn

I have a db table for users something like this.

ID      user_id     country_id      last_login
1       132          2              2021-07-14 10:20:32
2       121          4              2021-07-15 13:49:08
3       124          2              2021-07-14 13:49:31
4       123          5              2021-07-05 13:49:40
5       132          2              2021-07-14 13:50:32
6       132          2              2021-07-15 09:34:23

Here I want to make somekind of sql query so that it would show the count of numbers of ID for the user with same country_id on the same date

So the output should be something like this

ID  Count     user_id       country_id      last_login
1    3          132          2              2021-07-14
2    1          121          4              2021-07-15
4    1          123          5              2021-07-05
6    1          132          2              2021-07-15

Upvotes: 0

Views: 35

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

If you are using MySQL 8+ and have access to analytic functions, we can try:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY country_id, DATE(last_login) ORDER BY ID) rn,
              COUNT(*) OVER (PARTITION BY country_id, DATE(last_login)) cnt
    FROM yourTable
)

SELECT ID, cnt AS Count, user_id, country_id, last_login
FROM cte
WHERE rn = 1;

On earlier versions of MySQL (but you should really upgrade):

SELECT t2.ID, t2.cnt AS Count, t1.user_id, t1.country_id, t1.last_login
FROM yourTable t1
INNER JOIN
(
    SELECT country_id, DATE(last_login) AS last_login_date, COUNT(*) AS cnt,
           MIN(ID) AS ID
    FROM yourTable
    GROUP BY country, DATE(last_login)
) t2
    ON t1.country_id = t2.country_id AND
       DATE(t1.login_date) = t2.last_login_date AND
       t1.ID = t2.ID;

Upvotes: 1

Related Questions