CODEWITHSUNDEEP
sqloracle-databasecount
Manuel Fernandez
Manuel Fernandez

Reputation: 3

Oracle SQL count cases by one column

In oracle SQL I have a table, FINAL, like such :

--------------------
--  ID      -- TYPE
--------------------
-- 123      -- A
-- 123      -- A
-- 123      -- B
-- 123      -- B
-- 123      -- C
-- 124      -- B
-- 124      -- B
-- 124      -- C
-- ...      -- ...

and I would like an output like such:

----------------------------------------------------------------------------------
-- Count distinct IDs -- count (type A) -- count (type B) -- count (type C)
-- 10000              -- 5000           -- 4000           -- 1000
----------------------------------------------------------------------------------

The part I am having trouble with is that once an ID is counted type A, it cannot be B or C. Once it is counted type B, it cannot be C and can't have been A. To be C, it must ever have been counted A or B.

Thus far I have something like

select
count(distinct FINAL.ID)
from 
FINAL

A, B, and C are the only possible values.

Upvotes: 0

Views: 314

Answers (1)

user5683823
user5683823

Reputation:

Something like this may work:

select count(*) as ct_id,
       count(case type when 'A' then 1 end) as ct_a,
       count(case type when 'B' then 1 end) as ct_b,
       count(case type when 'C' then 1 end) as ct_c
from (
       select   id, min(type) as type
       from     final
       group by id
     )
;

The subquery takes care of "distinct" (because it generates a single row for each distinct id), and it selects just the "least" type for each id. The outer query does the total count and the conditional counts.

Upvotes: 1

Related Questions