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?
Aucun commentaire:
Enregistrer un commentaire