SQL Count with Different Condition on single Query

Question

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.

Tim Biegeleisen · Accepted Answer · 2021-10-18 09:12:02Z

1

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;

answered Oct 18, 2021 at 9:12

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

3 Comments

Haminteu Over a year ago

Hi, thanks a lot. This one is correct! Do you have any idea for Linq syntax?

Tim Biegeleisen Over a year ago

@Haminteu I don't use LINQ very often, so off the top of my head, no, I don't know how to express conditional aggregation in a LINQ query.

Haminteu Over a year ago

just remove the linq question. I found it. Thanks a lot anyway. Cheers,

Sergey · Accepted Answer · 2021-10-18 09:10:54Z

0

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

answered Oct 18, 2021 at 9:10

Sergey

5,2801 gold badge9 silver badges12 bronze badges

Collectives™ on Stack Overflow

SQL Count with Different Condition on single Query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related