CODEWITHSUNDEEP
sqloracle-databaseoracle-apex
hkimberlin
hkimberlin

Reputation: 11

SUM the results of a CASE Statement

I am using the following code to count the records by Type in a table:

select null link, CASE when SUBSTR(PRJA_TYPE,1,3) ='CHO' then 'CHO'
  else PRJA_TYPE
END as label, 
COUNT(PRJA_TYPE) Contract_Type
from  "SPM"."PROJECT_AGREEMENTS"
where PRJA_ACTIVE = 1 and PRJA_TYPE IS NOT NULL
 group by PRJA_TYPE
order by PRJA_TYPE

It is giving me the following result:

LINK    LABEL   CONTRACT_TYPE

-       Ballpark   14 
-       CHO        7 
-       CHO        7 
-       CHO        6 
-       CHO        30 
-       CHO        11 
-       CHO        9 
-       CHO        1 
-       CHO        2 
-       LOA        9

This part is working as expected. I then need to SUM up the items in the Label Column that are marked CHO. So my final result would look like this:

LINK    LABEL   CONTRACT_TYPE

-       Ballpark   14 
-       CHO        73 
-       LOA        9

Any help would be much appreciated.

Upvotes: 1

Views: 557

Answers (3)

dd4711
dd4711

Reputation: 799

You can use your case statement in your group-clause:

select 
  null link, 
  CASE when SUBSTR(PRJA_TYPE,1,3) ='CHO' then 'CHO' else PRJA_TYPE END as label, 
COUNT(PRJA_TYPE) Contract_Type
from  "SPM"."PROJECT_AGREEMENTS"
where PRJA_ACTIVE = 1 and PRJA_TYPE IS NOT NULL
group by CASE when SUBSTR(PRJA_TYPE,1,3) ='CHO' then 'CHO' else PRJA_TYPE END
order by PRJA_TYPE

As an alternative (e.g. if you can't modify your query) you can use a second query to sum your numbers up:

select link, label, sum(CONTRACT_TYPE)
from (
--your first query
) x
group by link, label

Upvotes: 0

Ven
Ven

Reputation: 2014

cte would do i guess

with cte as
(select null link, CASE when SUBSTR(PRJA_TYPE,1,3) ='CHO' then 'CHO'
  else PRJA_TYPE
END as label, 
COUNT(PRJA_TYPE) Contract_Type
from  "SPM"."PROJECT_AGREEMENTS"
where PRJA_ACTIVE = 1 and PRJA_TYPE IS NOT NULL
 group by PRJA_TYPE
order by PRJA_TYPE
)

select link, label, sum(contract_type) contract_type from cte group by link,label

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You need to aggregate by the expression:

select null as link,
       (case when SUBSTR(PRJA_TYPE, 1, 3) = 'CHO' then 'CHO'
             else PRJA_TYPE
        end) as label, 
       COUNT(*) Contract_Type
from  "SPM"."PROJECT_AGREEMENTS"
where PRJA_ACTIVE = 1 and PRJA_TYPE IS NOT NULL
group by (case when SUBSTR(PRJA_TYPE, 1, 3) = 'CHO' then 'CHO'
               else PRJA_TYPE
          end)
order by label;

Upvotes: 1

Related Questions