SUM column values with group by aggregate taking care of null values

I have a table employees. The data is inserted as (in oracle):

ID EMP_NAME DEPT_NAME EFFORTS ACTIVITY_DAY 633 ALEX XYZ 30 13-May-15 633 ALEX XYZ 30 14-May-15 633 ALEX ABC 30 13-May-15 633 ALEX XYZ 0 15-May-15

But I need to fill the efforts as 30 for a single day, e.g. here 30 has been filled twice for 13-May-15 as ALEX is there for two dept_name. I need to modify the record like this:

ID EMP_NAME DEPT_NAME EFFORTS ACTIVITY_DAY 633 ALEX XYZ 30 13-May-15 633 ALEX XYZ 30 14-May-15 633 ALEX ABC 0 13-May-15 633 ALEX XYZ 0 15-May-15

--------------Solutions-------------

Based on your desired output, if I understand correctly, you want to update the EFFORTS column to zero when there exists more than one row for the EMP_NAME for a given ACTIVITY_DAY.

You could ROW_NUMBER() analytic function to rank the rows with the given condition, and then update the efforts for rows with rank > 1.

Let's see:

Setup

SQL> SELECT * FROM t;

ID EMP_ DEP EFFORTS ACTIVITY_
-------- ---- --- ---------- ---------
633 ALEX XYZ 30 13-May-15
633 ALEX XYZ 30 14-May-15
633 ALEX ABC 30 13-May-15
633 ALEX XYZ 0 15-May-15

SQL>

Driving query

SQL> SELECT t.*,
2 row_number()
3 OVER(PARTITION BY emp_name, activity_day ORDER BY activity_day) rn
4 FROM t;

ID EMP_ DEP EFFORTS ACTIVITY_ RN
-------- ---- --- ---------- --------- ----------
633 ALEX XYZ 30 13-May-15 1
633 ALEX ABC 30 13-May-15 2
633 ALEX XYZ 30 14-May-15 1
633 ALEX XYZ 0 15-May-15 1

SQL>

MERGE statement to update the table

SQL> MERGE INTO t
2 USING(
3 SELECT t.*,
4 row_number()
5 OVER(PARTITION BY emp_name, activity_day ORDER BY activity_day) rn
6 FROM t
7 ) u
8 ON (t.ID = u.ID
9 AND t.emp_name = u.emp_name
10 AND t.dept_name = u.dept_name
11 AND t.activity_day = u.activity_day)
12 WHEN MATCHED THEN UPDATE SET efforts = 0
13 WHERE u.rn > 1;

1 row merged.

SQL>

Let's check

SQL> SELECT * FROM t;

ID EMP_ DEP EFFORTS ACTIVITY_
-------- ---- --- ---------- ---------
633 ALEX XYZ 30 13-May-15
633 ALEX XYZ 30 14-May-15
633 ALEX ABC 0 13-May-15
633 ALEX XYZ 0 15-May-15

SQL>

So, this gives the desired output.

Category:mysql Time:2018-07-11 Views:1
Tags: mysql sql oracle

Related post

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.591 (s). 13 q(s)