CODEWITHSUNDEEP
sqlcount
Christian Helmann
Christian Helmann

Reputation: 19

SQL query - Count Job ID for Customer ID and Date

I need some help to count the number of job_id values for my specific customer_id and group them in the right date column.

The table looks like this (it only contains data for the last 3 months):

customer_id job_id Date
1 101 01-01-2024
1 102 12-12-2023
1 103 10-11-2023
2 104 25-12-2023
2 105 01-01-2024
1 106 01-12-2023
2 107 15-11-2023

I would like the query to be like this: a job_id count for each customer_id, and a count for each month the job_id is created.

customer_id job_id Nov-23 Dec-23 Jan-24
1 4 1 2 1
2 3 1 1 1

Upvotes: 0

Views: 203

Answers (1)

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

To get the required output, you can use conditional aggregation, also known as a pivot query. Here's how:

SELECT 
    customer_id,
    COUNT(job_id) AS total_jobs,
    SUM(CASE WHEN MONTH(STR_TO_DATE(`Date`, '%d-%m-%Y')) = MONTH(CURRENT_DATE - INTERVAL 2 MONTH) AND YEAR(STR_TO_DATE(`Date`, '%d-%m-%Y')) = YEAR(CURRENT_DATE - INTERVAL 2 MONTH) THEN 1 ELSE 0 END) AS 2nd_last_month,
    SUM(CASE WHEN MONTH(STR_TO_DATE(`Date`, '%d-%m-%Y')) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH) AND YEAR(STR_TO_DATE(`Date`, '%d-%m-%Y')) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) THEN 1 ELSE 0 END) AS last_month,
    SUM(CASE WHEN MONTH(STR_TO_DATE(`Date`, '%d-%m-%Y')) = MONTH(CURRENT_DATE) AND YEAR(STR_TO_DATE(`Date`, '%d-%m-%Y')) = YEAR(CURRENT_DATE) THEN 1 ELSE 0 END) AS current_month
FROM Table1
WHERE STR_TO_DATE(`Date`, '%d-%m-%Y') >= CURRENT_DATE - INTERVAL 2 MONTH AND STR_TO_DATE(`Date`, '%d-%m-%Y') <= CURRENT_DATE
GROUP BY customer_id;

https://www.db-fiddle.com/f/pJAJm9dJzGcq5pDGPm7pKV/0

Upvotes: 1

Related Questions