CODEWITHSUNDEEP
sqloracle-database
pyds_learner
pyds_learner

Reputation: 519

In oracle SQL , how to count the no of records based on conditions

I have table with below structure :

Col2
A
A
B
B
E
E

I wanted the SQL query to output me the following :

Internal    4
External    2
Total   6

Logic : If the values in the Col2 are A,B then it should be summed up as Internal , If E then it should be summed up as External.

Upvotes: 2

Views: 325

Answers (5)

Shrirang
Shrirang

Reputation: 1336

select sum(Col2Count) as Internal from (SELECT Col2 as Col2, count( Col2 ) as Col2Count
FROM tablename group by Col2) where Col2 in (A,B);

This will give you result as :

Internal 4

Upvotes: 0

Marmite Bomber
Marmite Bomber

Reputation: 21043

To map your column values use DECODE, simple providing the list of the original and new values for the column.

select decode(col2,'A','Internal','B','Internal','E','External') col from tab

To calculate the total you do not need to rescan the whole table (performance drops to the half) but use group by rollup that calculates the Total 

with t as ( 
select decode(col2,'A','Internal','B','Internal','E','External') col from tab)
select nvl(col,'Total') col, count(*) cnt
from t
group by rollup (col)

Result

COL             CNT
-------- ----------
External          2
Internal          4
Total             6

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

try like below using union all and make customize group

   select case when col2 in ('A','B') then 'Internal' else 'External' end,
    count(*) as result
   from table_name
   group by case when col2 in ('A','B') then 'Internal' else 'External' end
   union all
  select 'total', count(*) from table_name

Upvotes: 1

VBoka
VBoka

Reputation: 9083

select 'Internal' "summed up as"
       ,sum(case when Col2 in ('A', 'B') then 1
            else 0
            end) "sum"
from test
union
select 'External' "summed up as"
       ,sum(case when Col2 = 'E' then 1
            else 0
            end) "sum"
from test
union
select 'Total' "summed up as"
       , count(Col2) "sum"
from test;

Here is a DEMO

Upvotes: 1

juergen d
juergen d

Reputation: 204746

select sum(case when col2 in ('A', 'B') then 1 else 0 end) as internal,
       sum(case when col2 = 'E' then 1 else 0 end) as external,
       count(col2) as total
from your_table

Upvotes: 1

Related Questions