Am using Oracle 12c, below is the column:
CENTER_ID UNIT EMPLOYEEID LESSON MINS_STUDIED SECTION
I234 4 G01234 4.1 5 EX
I234 4 G01234 4.1 5 LN
I234 4 G01234 4.1 5 VO
I234 4 G0123 4.2 5 EX
I234 4 G0123 4.2 5 LN
I234 4 G0123 4.2 5 VO
I2345 5 G023 5.2 12 EX
I2345 5 G023 5.2 12 LN
I2345 5 G023 5.2 12 VO
From record 1 to 6, it contains 2 distinct employee_id who studied unit 4. In this case, I need total minutes spent per unit (center_id wise).
But I want to add only distinct MINS_STUDIED based on EMPLOYEE_ID.
So, total minutes spent for center_id I234 for unit 4 is (5+5)=10 and for center_id I2345 for unit 5 is 12.
I used the below query
SELECT
COUNT(DISTINCT EMPLOYEE_ID),CENTER_ID,UNIT,
SUM(DISTINCT MINS_STUDIED)
FROM
TEST_DATA
GROUP BY
CENTER_ID,DBD.UNIT;
The above query returns
center_id UNIT DISTINCT(EMPLOYEE_ID) TOTAL_MINS_STUDIED
I234 4 2 5
I2345 5 1 12
But, I need the output like below:
center_id UNIT DISTINCT(EMPLOYEE_ID) TOTAL_MINS_STUDIED
I234 4 2 10
I2345 5 1 12
See Question&Answers more detail:os