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.

Answer

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