How can I get multiple COUNT's in a single SQL query with multiple WHERE conditions?

I need to get a report on users' activity on a web app.

First, I have a table that counts the number of times a user logs on our app that looks like this:

user_activity
-----------------------------------------------------
| ID_login | Username | Date       | Hour     | ... |
|---------------------|------------|----------|-----|
|        1 | john     | 2019-03-01 | 08:49:24 | ... |
|        2 | john     | 2019-03-01 | 14:12:49 | ... |
|        3 | jane     | 2019-03-03 | 10:02:31 | ... |
|      ... | ...      | ...        | ...      | ... |
-----------------------------------------------------

What I want to do is have a report that will give me not just the total amount of login per user for a set time period. That I can already do. Where I'm stuck is when I try to get a report for each month of the previous year where months are in separate columns.

I would want the table generated by my query to look like this:

user_activity
--------------------------------------------------------
| Username | login_total | login_jan | login_feb | ... |
|------------------------|-----------|-----------|-----|
| john     |        4827 |       164 |       191 | ... |
| james    |        3866 |        92 |       144 | ... |
| jane     |        2979 |       104 |        92 | ... |
| ...      |         ... |       ... |       ... | ... |
--------------------------------------------------------

So as you can see, I was thinking about using the login_total value to use for ORDER BY.

So here's what I have at this point.

SELECT
    user_activity.Username,
    COUNT(user_activity.ID_login) AS login_total

FROM
    user_activity

WHERE
    user_activity.Date >= '2018-01-01'
    AND
    user_activity.Date <= '2018-12-31'

How do I get another column with the COUNT, but with different criteria? Where am I supposed to put in those criteria? I've tried using IF statements and HAVING statements, but it didn't work.

I was thinking something like this...?

SELECT
    user_activity.Username,
    COUNT(
        IF
            (user_activity.Date >= '2019-01-01'
            AND
            user_activity.Date <= '2019-01-31')
        user_activity.ID_login) 
        AS login_jan,

    COUNT(
        IF
            (user_activity.Date >= '2019-02-01'
            AND
            user_activity.Date <= '2019-02-28')
        user_activity.ID_login) 
        AS login_feb,
    ...

FROM
    user_activity

Didn't seem to work either... Is there something I'm missing?

Solution

You are trying to create a PIVOT table which you can do in MySQL using conditional aggregation. Note that you can simplify your expressions by using the MONTH and YEAR functions:

SELECT u.Username,
       COUNT(*) AS login_total,
       SUM(MONTH(u.Date) = 1) AS login_jan,
       SUM(MONTH(u.Date) = 2) AS login_feb,
       ...
       SUM(MONTH(u.Date) = 12) AS login_dec
FROM user_activity u
WHERE YEAR(u.Date) = 2018
GROUP BY u.Username

This query also takes advantage of the fact that in a numeric context MySQL treats a boolean expression as 1 (true) or 0 (false), allowing us to SUM those values.