Using Count/group by in case expression

Question

I am trying to show all the job positions held by the staff member except 'Relief Teacher' - if the staff is holding more than one job positions. And show Relief teacher if the teacher (staff is holding only one job title of Relief Teacher).

I used a case expression and then used Select statement in it which uses group by, in order to count the number of job positions held by the staff member. But I get an error message saying the subquery returned more than 1 value. Also, I am trying to exclude 'Relief Teacher' from the JobPositions When I am displaying all the Job Positions when using STUFF() function. But I am not able to get that. The second When in Case Statement will show all the job positions when using STUFF. It does not exclude Relief Teacher title from there.

SELECT jobpositions.staffid, 
               jobpositions.StaffNameExternal,
               (Case when ((select Count(StaffId) from dbo.vStaffJobPositions group by staffid) =1 and (jobpositiondescription like 'Relief Teacher'))
                then 'Relief Teacher'
                when ((select Count(StaffId) from dbo.vStaffJobPositions group by staffid) >1 )
                then (STUFF((SELECT ' / ' + JobPositionDescription
                FROM dbo.vStaffJobPositions
                FOR XML PATH('')),1,3, '')) 
                end) as StaffPosition
    FROM dbo.vStaffJobPositions JobPositions

I have 2 problems with the above code-

The Count and groupBy are giving error of Subquery returning more than one value.
I want to show only the Job positions held by the staff except Relief Teacher using STUFF()- which is stuff different positions held by them. But I cant EXCLUDE 'Relief Teacher' from there (when they hold more than one job title)

sticky bit · Accepted Answer · 2019-07-31 23:37:47Z

1

You don't correlate your subqueries, i.e. the return the result for all staff not just the current one.

To check if a staff member is only a relief teacher, you can also just check if the minimum and maximum of their roles is equal and a relief teacher. In the subquery that gets the list of positions just filter relief teachers out in the WHERE clause.

SELECT jp1.staffid, 
       jp1.staffnameexternal,
       CASE
         WHEN min(jp1.jobpositiondescription) = max(jp1.jobpositiondescription)
              AND max(jp1.jobpositiondescription) = 'Relief Teacher' THEN
           max(jp1.jobpositiondescription)
         ELSE
           stuff((SELECT ' / ' + jp2.jobpositiondescription
                         FROM dbo.vstaffjobpositions jp2
                         WHERE jp2.staffid = jp1.staffid
                               AND jp2.jobpositiondescription <> 'Relief Teacher'
                         FOR XML
                             PATH ('')),
                 1,
                 3,
                 '')
       END staffposition
       FROM dbo.vstaffjobpositions jp1
       GROUP BY jp1.staffid, 
                jp1.staffnameexternal;

edited Jul 31, 2019 at 23:37

answered Jul 31, 2019 at 23:31

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PrettyCode Over a year ago

Great! Thank you for explaining where I was making mistake and many thanks to show how to solve this with the result which I expect.

Ali Al-Mosawi · Accepted Answer · 2019-07-31 23:26:03Z

The group by is giving more than one record for the count, and you are not filtering for the same user row by row,

So please use the below:- I created a memory table to show some results, you dont need this part

Declare @vStaffJobPositions table(
staffid int,
StaffNameExternal varchar(max),
jobpositiondescription varchar(max)
)

And here added some test data as you did not provide any

insert into @vStaffJobPositions values
(1,'S1','Relief Teacher'),
(1,'S1','somthing else1'),
(1,'S1','somthing else2'),
(1,'S1','somthing else3'),
(1,'S1','Relief Teacher'),
(2,'S2','Relief Teacher'),
(3,'S3','somthing else1')

The SQL part that you can use is as below, change only the table name to yours.

select distinct 
    staffid
    ,StaffNameExternal 
    ,case when (select COUNT(*) from @vStaffJobPositions c where c.staffid=x.staffid and jobpositiondescription like 'Relief Teacher')=1 
            then 'Relief Teacher'
            else STUFF((SELECT ' / ' + JobPositionDescription FROM @vStaffJobPositions a 
        where x.staffid=a.staffid and a.jobpositiondescription<>'Relief Teacher' FOR XML PATH('')),1,3, '')
        end as StaffPosition
from @vStaffJobPositions x

For the above the results were as below:-

staffid StaffNameExternal   StaffPosition
1   S1  somthing else1 / somthing else2 / somthing else3
2   S2  Relief Teacher
3   S3  somthing else1

S2 is only a Relief Teacher
S1 have a group of positions including 'Relief Teacher' that we are excluding
S3 have one but not a 'Relief Teacher'

hope it helps.

Excellent! Thank you for elaborating and explaining with the temp memory table you created. M

Collectives™ on Stack Overflow

Using Count/group by in case expression

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related