Question 316824 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Split a column with dates into multiple columns by month. SQL

I’m trying to validate the users logged in based on month. SQL

current df

user id             date
P1302              2023-11-01
P1302              2023-10-01
P1302              2023-09-01
P1302              2023-08-01
P1302              2023-07-01
P1302              2023-06-01
P1301              2023-11-01
P1301              2023-10-01
P1301              2023-08-01
P1301              2023-07-01
P1301              2023-06-01

expected result

user id     Jun     Jul     aug   sep   oct     nov
1302         1       1       1     1     1       1
1301         1       1       1     0     1       1
Tags:

4

Answers


  1. 0

    You can do it with conditional aggregation:

    select
    user_id,
    max(extract(month from date) =  1) as `jan`,
    -- ...
    max(extract(month from date) = 12) as `dec`
from t
group by user_id
    Login or Signup to reply.
  2. 0

    One common approach to your problem is to use count over a case expression:

    select user_id
     , count(case month(dt) = 1 then 1 end) as jan
     , count(case month(dt) = 2 then 1 end) as feb
     , ...
from ...
group by user_id
    Login or Signup to reply.
  3. 0 
    SELECT
    user_id,
    MAX(CASE WHEN MONTH(date) = 6 THEN 1 ELSE 0 END) AS Jun,
    MAX(CASE WHEN MONTH(date) = 7 THEN 1 ELSE 0 END) AS Jul,
    MAX(CASE WHEN MONTH(date) = 8 THEN 1 ELSE 0 END) AS Aug,
    MAX(CASE WHEN MONTH(date) = 9 THEN 1 ELSE 0 END) AS Sep,
    MAX(CASE WHEN MONTH(date) = 10 THEN 1 ELSE 0 END) AS Oct,
    MAX(CASE WHEN MONTH(date) = 11 THEN 1 ELSE 0 END) AS Nov
FROM your_table
GROUP BY user_id
ORDER BY user_id;
    Login or Signup to reply.
  4. 0

    You can use the SUM function with conditional statements (MONTH(date) = month_number) to count the occurrences of logins for each user in each month.

    SELECT 
    user_id,
    SUM(MONTH(date) = 6) AS Jun,
    SUM(MONTH(date) = 7) AS Jul,
    SUM(MONTH(date) = 8) AS Aug,
    SUM(MONTH(date) = 9) AS Sep,
    SUM(MONTH(date) = 10) AS Oct,
    SUM(MONTH(date) = 11) AS Nov
FROM 
    user_activity
GROUP BY 
    user_id;

    See this db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search