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

1 Answer 1

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
  • 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, 2021 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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