I need to count the number of rows for each employee where the status = 9 and the date is the same as other rows within that employee_id where the status = 9. I also want the employee_id and count_all, a count of all the rows for each employee.
Figuring out how to get the count_same_date_status_9 column is where I am stuck. These dates are made up just for the example so I dont want to put them explicitly into the query.
My table:
employee_id status date
1 9 10/19/2020
1 7 07/16/2001
1 9 10/19/2020
2 5 08/11/2011
2 9 12/25/2012
2 9 11/19/2013
3 5 06/05/2016
3 4 01/01/2021
4 9 02/15/2018
4 9 02/15/2018
4 9 02/15/2018
I want to return the following:
employee_id count_same_date_status_9 count_all
1 2 3
2 0 3
3 0 2
4 3 3
You can use:
SELECT employee_id,
MAX( CASE WHEN status = 9 AND cnt > 1 THEN cnt ELSE 0 END ) AS count_9,
SUM( cnt ) AS count_all
FROM (
SELECT employee_id,
status,
dt,
COUNT(*) AS cnt
FROM table_name
GROUP BY employee_id, status, dt
)
GROUP BY employee_id
ORDER BY employee_id;
Which, for your sample data:
CREATE TABLE table_name ( employee_id, status, dt ) AS
SELECT 1, 9, DATE '2020-10-19' FROM DUAL UNION ALL
SELECT 1, 7, DATE '2001-07-16' FROM DUAL UNION ALL
SELECT 1, 9, DATE '2020-10-19' FROM DUAL UNION ALL
SELECT 2, 5, DATE '2011-08-11' FROM DUAL UNION ALL
SELECT 2, 9, DATE '2012-12-25' FROM DUAL UNION ALL
SELECT 2, 9, DATE '2013-11-19' FROM DUAL UNION ALL
SELECT 3, 5, DATE '2016-06-05' FROM DUAL UNION ALL
SELECT 3, 4, DATE '2021-01-01' FROM DUAL UNION ALL
SELECT 4, 9, DATE '2018-02-15' FROM DUAL UNION ALL
SELECT 4, 9, DATE '2018-02-15' FROM DUAL UNION ALL
SELECT 4, 9, DATE '2018-02-15' FROM DUAL;
Outputs:
EMPLOYEE_ID | COUNT_9 | COUNT_ALL
----------: | ------: | --------:
1 | 2 | 3
2 | 0 | 3
3 | 0 | 2
4 | 3 | 3
db<>fiddle here
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments