November 15, 2013 at 9:06 am
Hi Team,
I have a table as mentioned below with sample data
CREATE TABLE EMPLOYEE_TEST(
EMPID INT,
EMPNAME VARCHAR(10),
SALARY INT,
DEPARTID INT
)
INSERT INTO EMPLOYEE_TEST VALUES(1,'A',NULL,100)
INSERT INTO EMPLOYEE_TEST VALUES(1,null,1000,100)
INSERT INTO EMPLOYEE_TEST VALUES(3,'B',1000,101)
INSERT INTO EMPLOYEE_TEST VALUES(4,'B',1000,101)
SELECT EMPID,MAX(EMPNAME),MAX(SALARY),MAX(DEPARTID) FROM EMPLOYEE_TEST
GROUP BY EMPID,DEPARTID
This gives me output,
EMPID EMPNAME SALARY DEPARTID
1 A 1000 100
3 B 1000 101
4 B 1000 101
Now, when I have data as mentioned below,
DELECT FROM EMPLOYEE_TEST
INSERT INTO EMPLOYEE_TEST VALUES(1,'A',NULL,100)
INSERT INTO EMPLOYEE_TEST VALUES(1,null,1000,NULL)
INSERT INTO EMPLOYEE_TEST VALUES(3,'B',1000,101)
INSERT INTO EMPLOYEE_TEST VALUES(4,'B',1000,101)
SELECT EMPID,MAX(EMPNAME),MAX(SALARY),MAX(DEPARTID) FROM EMPLOYEE_TEST
GROUP BY EMPID,DEPARTID
EMPID EMPNAME SALARY DEPARTID
1 NULL 1000 NULL
1 A NULL 100
3 B 1000 101
4 B 1000 101
I can understand because NULL in DEPARTID is treated as unique value. But here also, I would like to see a single row for EMPID 1(as shown in the first result). Max() is not doing the job, is there any work around to do it. Please suggest
Cheers
Jim
November 15, 2013 at 9:16 am
the issue is that you have bad data. Null in the name column means that SQL doesn't know if this matches the other name or not. I'd argue you should be cleaning your data up first. There are possibly ways you could enable a workaround with WHERE or HAVING clauses, but will that return incorrect results? You should consider that and perhaps have a cleansing of your data that updates or removes NULL values.
November 15, 2013 at 9:25 am
Hi Steve,
Where clause will not help. I don't want to delete the EMPID 1 where departid is NULL. I want it to get merged with the EMPID 1 where departid is 100.
I thought of Max() to remove null values and merge it but it is not working because of Group by.
Any suggestion?
This is only sample data to explain the senario.
Regards
Jim
November 22, 2013 at 11:45 am
What does merge mean? It's not really clear from your sample data. I'm guessing you mean that if there's a null, look for a value in another row, but is this the "cleanliness" of your data? Are you positive that all rows that have an empid of "1" don't have conflicting data in other rows?
If it's dealing with NULLs, what you might need are a couple passes, or a complex subquery, that grabs data for each field from a row that isn't null. For example.
;
WITH NameCTE ( empid, empname )
AS ( SELECT empid
, EMPNAME
FROM dbo.EMPLOYEE_TEST
WHERE EMPNAME IS NOT NULL
) ,
salarycte ( empid, salary )
AS ( SELECT empid
, salary
FROM dbo.EMPLOYEE_TEST
WHERE salary IS NOT NULL
) ,
deptcte ( empid, departid )
AS ( SELECT empid
, departid
FROM dbo.EMPLOYEE_TEST
WHERE departid IS NOT NULL
)
SELECT DISTINCT e.empid
, n.empname
, salary = MAX(s.salary)
, d.departid
FROM dbo.EMPLOYEE_TEST e
INNER JOIN NameCTE n
ON e.EMPID = n.empid
INNER JOIN salarycte s
ON e.EMPID = s.empid
INNER JOIN deptcte d
ON e.EMPID = d.empid
GROUP BY e.EMPID
, n.empname
, d.departid
, e.departid
November 22, 2013 at 11:55 am
Jim1234 (11/15/2013)
SELECT EMPID,MAX(EMPNAME),MAX(SALARY),MAX(DEPARTID) FROM EMPLOYEE_TEST
GROUP BY EMPID,DEPARTID
Try removing DEPARTID from your GROUP BY clause.
I agree with Steve, you need to review why do you have bad data and correct it. What will have if you have something like this:
INSERT INTO EMPLOYEE_TEST VALUES(1,'A',NULL,100)
INSERT INTO EMPLOYEE_TEST VALUES(1,NULL,1000,NULL)
INSERT INTO EMPLOYEE_TEST VALUES(1,NULL,1000,101)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply