How does mysql get data of different user_id while grouping date and user_id at the same time according to date?

grouped by date user_id to get
date user_id
2019-01-08 1518910
2019-01-08 1543358
2019-01-08 1543358
2019-01-08 1563574
2019-01-08 1563574
2019-01-08 1562939
2019-01-08 1560924
2019-01-08 1524686
2019-01-08 1554495
2019-01-08 1539485
2019-01-08 1539485
2019-01-08 1543358
2019-01-08-1543356
2019-01-01-1539485 br > 2019-01-1539485
2019-01-1539485
1539485
2019-01-08 1539485
2019-01-08 1539485
2019-01-08 1543358

want to get

date user_id total
2019-01-08 1518910 9
2019-01-08 1543358 9
2019-01-08 1543358 9
2019-01-08 1518910 9
2019-01-08 1563574 9
2019-01-08 1563574 9
2019-01-08 1562939 9
2019-01-08 1560924 9
2019-01-08 1524686 9
2019-01 1-08 1524686 9
2019-01 1 08 1539485 9
2019-10 08-1539485 9
2019-1 08 154339
2019-01 08-15433 9
2019-1 008-15349 br 08 1539485 9
2019-01-08 1539485 9
2019-01-08 1543358 9
9 indicates different number of users

May.20,2022

Let me first understand the requirements. Data 1 is the original data, and data 2 is to add a column while taking out the original data. This column of data is the number of different uid under each date

if you understand correctly, you can use the following SQL implementation (the table name is assumed to be test_group )

select
    b.date, b.user_id, c.total 
from
    test_group b 
join 
    (
        select
            date, count(*) as total
        from
            (
                select
                    date, user_id from test_group
                group by
                    user_id,date
            ) a
        group by
            date
    ) c
on
    b.date = c.date;

ps: complex sql may have performance problems, so consider it yourself

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-7b4890-1e9ff.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-7b4890-1e9ff.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?