CODEWITHSUNDEEP
sqloracle-databasegroup-byoracle10gaggregate-functions
bryce
bryce

Reputation: 23

Get separate count for each condition within group

I am trying to get a view of table information from and Oracle 10g table that lists the counts of specific values of a column in their own columns with each row being the group value.

for example: The first select is :

SELECT processed_by, count(priority) as P2
FROM agreement_activity
WHERE priority = '2'
GROUP BY processed_by

Which outputs:

PROCESSED_BY                           P2
------------------------------ ----------
Alicia                                  2
Christine                               2

The second select is:

SELECT processed_by, count(priority) as P1
FROM agreement_activity
WHERE priority = '1'
GROUP BY processed_by

Which outputs:

PROCESSED_BY                           P1
------------------------------ ----------
Bonita                                  2
Alicia                                  6
Christine                               2

What I am looking for is to output those values as the following:

PROCESSED_BY                           P1         P2
------------------------------ ---------- ----------
Bonita                                  2          
Alicia                                  6          2        
Christine                               2          2

Is that possible?

Upvotes: 2

Views: 78

Answers (2)

Mandz
Mandz

Reputation: 195

this is how I implement the sql. I'm using firebird code but I think you can convert the code into your sql

SELECT
a.equipmentid,
a.name equipname,
w1.countwarranty1 ,
w2.countwarranty2
FROM TBL_EQUIPMENTMST a 
inner JOIN
    (select c.equipmentid, count(c.WARRANTYID) countwarranty1 from tbl_equipwarranty c where c.serviceproduct='1' group by c.equipmentid) w1
ON w1.equipmentid = a.equipmentid
inner JOIN
     (select d.equipmentid, count(d.WARRANTYID) countwarranty2 from tbl_equipwarranty d where d.serviceproduct='2' group by d.equipmentid) w2 
ON w2.equipmentid = a.equipmentid
inner JOIN
(select e.equipmentid, count(e.equiplocationid) countlocation from tbl_equiplocation e group by e.equipmentid) w3
ON w3.equipmentid = a.equipmentid

this is the output

enter image description here

My warranty table has only 2 warranty for equipment each that's why its only showing 2.

you can also inner join to the same table with different count. as you can see my warranty has multiple warranty where serviceproduct is different on each table

if I edit your code it will be like this

SELECT a.processed_by, b.priority as p2, c.priority as p1
FROM agreement_activity a
inner join
 (
  SELECT w1.processed_by, count(w1.priority) as P2 FROM agreement_activity w1
  WHERE w1.priority = '2' GROUP BY w1.processed_by
 ) b 
on a.processed_by = b.processed_by
inner join
 (
  SELECT w2.processed_by, count(w2.priority) as P2 FROM w2.agreement_activity
  WHERE w2.priority = '1' GROUP BY w2.processed_by
 ) c 
on a.processed_by = c.processed_by

test it

Upvotes: 0

potashin
potashin

Reputation: 44581

You can use sum with the case expression to get conditional count:

select processed_by
     , sum(case when priority = 1 then 1 else 0 end) as P1
     , sum(case when priority = 2 then 1 else 0 end) as P2
from agreement_activity
group by processed_by

P.S. If you don't care that P1 or P2 maybe null instead of 0 you can omit else in both expressions.

Upvotes: 2

Related Questions