CODEWITHSUNDEEP
sqlsql-server
Umesh Patil
Umesh Patil

Reputation: 10685

Count with GroupBy Query

Below are 2 tables-

JOBS
JobID ProcessID Status
j1    p1          Start 
j2    p2          Stop
j3    p2          Running
j4    p1          Start
j4    p1          Stop

Table 2

PROCESSES
ProcessID  Name    ProcessGroup
P1         P1Name   G1
P2         P2Name   G2
P3         P3Name   G1

Expected outcome- 

Process StartCount  StopCount
P1Name      2           1
P2Name      0           1

I am trying to use group by query for above output.

select jobId,p.Name, COUNT(j.j.JobID) as StartCount
from JOBS j,
PROCESSES p
where j.Status = 'Start' and
      j.ProcessID= p.ProcessID
group by j.Status

In a single query, I can get a count of only Start or StopCount. Can I get both in a single query?

Upvotes: 0

Views: 43

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use conditional aggregation for this:

select p.Name, 
       count(case when Status = 'Start' then 1 end) as StartCount,
       count(case when Status = 'Stop' then 1 end) as StopCount
from JOBS j
join PROCESSES p on j.ProcessID = p.ProcessID      
group by p.Name

Upvotes: 5

Related Questions