Oracle SQL . Count based on two columns

Question

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.

General GROUP BY rule: "If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function." — jarlh
– jarlh, Commented Apr 12, 2016 at 6:39

Utsav · Accepted Answer · 2016-04-12 06:44:49Z

2

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,

edited Apr 12, 2016 at 6:44

Utsav

8,1632 gold badges22 silver badges40 bronze badges

answered Apr 12, 2016 at 6:39

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

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

1 Comment

neha dhawan Over a year ago

That worked. I replaced the decode substr code with case as suggested .SELECT CASE data_center WHEN like 'EM%' THEN 'EMEA' WHEN like 'AP%' THEN 'APAC' WHEN like 'US%' THEN 'AMERICA' , Good learning for me. Thank you

Collectives™ on Stack Overflow

Oracle SQL . Count based on two columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related