How to populate columns based on different where conditions-CodePudding

I have a user table with a last_login column.
I have an additional table called group.
There is also a join table group_user

I have the following query:

select count(u.id), group.name
from user u
left join group_user gu where gu.user_id = u.id
left join group g where g.id = gu.group_id  
where u.last_login is not null
group by g.name

This generates total users logged in by group.

I then update the query to where u.last_login is null

Which generates total users not logged in.

How would I go about pulling all of the data at once with the following columns:

count(users), count(logged in), count(not logged in), % of total users that have logged in, group name

CodePudding user response：

Try something like this:

SELECT
    g.id AS group_id,
    MAX(g.name) AS group_name,
    COUNT(u.id) AS count_users,
    SUM(u.logged_in) AS count_logged_in,
    COUNT(u.id) - SUM(u.logged_in) AS count_not_logged_in,
    100 * SUM(u.logged_in) / COUNT(u.id) AS percent_logged_in

FROM
    (
    SELECT 
        id, 
        CASE WHEN u.last_login IS NULL THEN 0 ELSE 1 END AS logged_in
    FROM
        user
    ) u
    LEFT JOIN group_user gu ON u.id = gu.user_id
    LEFT JOIN group g ON g.id = gu.group_id
GROUP BY
    g.id -- since two groups could have the same name
;