CODEWITHSUNDEEP
sqloracle-databasecount
neha dhawan
neha dhawan

Reputation: 13

Oracle SQL . Count based on two columns

I have a table:

JIRA_KEY   DATACENTER STATUS 
  1         US        CLOSED
  2         EM        CLOSED
  3         AP        CLOSED
  4         US        CLOSED
  5         EM        IN PROGRESS
  6         AP        CANCELLED
  7         US        IN PROGRESS
  8         US        CANCELLED  
  9         AP        CANCELLED  
 10         AP        IN PROGRESS

Expected Output:

Datacenter TotalJiras Closed InProgress Cancelled
EMEA          2       1        1           0
APAC          4       1        1           2
AMERICA       4       2        1           1

I tried creating a view:

create or replace view VW_JIRA_STATUS_BK AS 
SELECT count(JIRA_KEY) JIRA ,
  decode (substr(data_center,1,2),'EM', 'EMEA', 'AP', 'APAC', 'US', 'AMERICA') as REGION, 
  status 
from hw_jira_status 
group by data_center , status;

select * from VW_JIRA_STATUS_BK

does not display the result as expected.

Any help would be appreciated.

Upvotes: 0

Views: 80

Answers (1)

dnoeth
dnoeth

Reputation: 60513

You need conditional aggregates using CASE, one for each result column:

create or replace view VW_JIRA_STATUS_BK AS 
SELECT
   decode (substr(data_center,1,2),'EM', 'EMEA', 'AP', 'APAC', 'US', 'AMERICA') as REGION, 
   count(JIRA_KEY) AS TotalJiras,
   COUNT(CASE WHEN Status = 'CLOSED' THEN 1 END) AS Closed,
   COUNT(CASE WHEN Status = 'IN PROGRESS' THEN 1 END) AS InProgress,
   COUNT(CASE WHEN Status = 'CANCELLED' THEN 1 END) AS Cancelled
from hw_jira_status 
group by data_center;  -- don't group by "Status"

Why do you apply a SUBSTR on data_center if it's two characters anyway?

Btw, I pefer Standard SQL's CASE over Oracle's deprecated DECODE:

CASE data_center
  WHEN 'EM' THEN 'EMEA'
  WHEN 'AP' THEN 'APAC'
  WHEN 'US' THEN 'AMERICA'
END as REGION,

Upvotes: 2

Related Questions