SQL Count with Different Condition on single Query-CodePudding

I have a query like:

SELECT          a.EmployeeId,
                count(a.JobStatusId) cTotOut
FROM            ActivityRecord a
where           a.jobstatusid <> 5
GROUP BY        a.EmployeeId

SELECT          a.EmployeeId,
                count(a.JobStatusId) cTotHis
FROM            ActivityRecord a
where           a.jobstatusid = 5
GROUP BY        a.EmployeeId

The result on my PC is like:

I want the queries above become a single query and become something like:

---------------------------------------
EmployeeId       cTotOut       cTotHis
---------------------------------------
A                7             5
B                5             7
C                4             8

I have a question:
Is it possible with two different condition into a single query?

Please advice.
Thank you.

CodePudding user response：

Use conditional aggregation:

SELECT
    EmployeeId,
    COUNT(CASE WHEN jobstatusid <> 5 THEN 1 END) AS cTotOut,
    COUNT(CASE WHEN jobstatusid = 5  THEN 1 END) AS cTotHis
FROM ActivityRecord
GROUP BY EmployeeId;

CodePudding user response：

SELECT          a.EmployeeId,
            SUM(
             CASE
               WHEN a.JobStatusId<>5 THEN 1
               ELSE 0 END) cTotOut,
           SUM(
             CASE
               WHEN a.JobStatusId=5 THEN 1
               ELSE 0 END)cTotHis
  FROM            ActivityRecord a
  GROUP BY        a.EmployeeId

You can try this. Sorry, about LinQ I know nothing, if it is possible and how