I have a query and which give the count greater than 1, but what I expect is I need the result to be based on particular column(Rollno.) How to achieve it.
Table Studies
x
NAME RollNo DeptType InternalStaff_1 InternalStaff_2
----------- ----------- ----------- --------------- ---------------
Anu 5 CompSci Eve Antony
Joy 13 Architecture Elizabeth George
Adam 2 Mech Grady Lisa
Adam 2 Mech Grady Kim
Anu 5 CompSci Eve Antony
The below query gives me Count but not as expected
SELECT DISTINCT S.Name
, S.RollNo
, COUNT(S.RollNo) AS [Count]
, S.DeptType
, S.InternalStaff_1
, S.InternalStaff_2
FROM DataMining.dbo.Studies S
WHERE StartDate >= '20210325'--@StartDate
AND StartDate <= '20210407'--@EndDate
GROUP BY S.Name, S.RollNo, S.DeptType, S.InternalStaff_1, S.InternalStaff_2
HAVING COUNT(S.RollNo) > 1
ORDER BY RollNo
The query gave me the below result
NAME RollNo Count DeptType InternalStaff_1 InternalStaff_2
----------- ----------- ----------- ----------- --------------- ---------------
Anu 5 2 CompSci Eve Antony
But the expected result is
NAME RollNo Count DeptType InternalStaff_1 InternalStaff_2
----------- ----------- ----------- ----------- --------------- ---------------
Anu 5 2 CompSci Eve Antony
Adam 2 2 Mech Grady NULL
As you can see the expected result is having a different InternalStaff_2 name for Adam which is not considered on the present result.
May I know how to over come this?
Note: I need the results to be displayed based on Rollno
but I also need the InternalStaff_2
to be included in the result.
Advertisement
Answer
Hmmm . . . If I understand correctly, you want NULL
if the internal staff columns do not match. That would be:
SELECT S.Name, S.RollNo, COUNT(*) AS [Count], S.DeptType,
(CASE WHEN MIN(S.InternalStaff_1) = MAX(S.InternalStaff_1) THEN MIN(S.InternalStaff_1) END) as InternalStaff_1,
(CASE WHEN MIN(S.InternalStaff_2) = MAX(S.InternalStaff_2) THEN MIN(S.InternalStaff_2) END) as InternalStaff_2
FROM DataMining.dbo.Studies S
WHERE StartDate >= '20210325' AND --@StartDate
StartDate <= '20210407' --@EndDate
GROUP BY S.Name, S.RollNo, S.DeptType
HAVING COUNT(*) > 1
ORDER BY RollNo;
Here is a db<>fiddle that shows that this basically works.