0

Now I am using this query in MySQL 5.7 to count login count:

select 
(
    select sum(login_count)
    from (
        select avg(login_count) as login_count
        from report_user b
        -- where b.user_id = a.user_id
        group by user_id,date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d')
    ) as login_count
) as login_count
from report_user a
group by user_id

when I calculate the inner login count, I just want to calculate by per user, then the user_id is specified, the result is correct. But when have many users in this table, the result is not correct. How to pass the outer user id into inner query in execute the query? I want this inner sql only calculate each user:

select sum(login_count)
    from (
        select avg(login_count) as login_count
        from report_user b
        -- where b.user_id = a.user_id
        group by user_id,date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d')
    ) as login_count
0

Using this sql solve my problem:

select 
(
    select sum(login_count)
    from (
        select avg(login_count) as login_count,user_id
        from report_user b
        -- where b.user_id = a.user_id
        group by user_id,date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d')
    ) as login_count
    where login_count.user_id = a.user_id
) as login_count
from report_user a
group by user_id
  • 1
    Don't forget to mark your own answer as correct - it might help others with a similar problem in the future. – Vérace Jan 14 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.