René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Multiple counts in a table

create table myTable (
  this  Varchar2(10),
  that  Varchar2(10)
);

insert into myTable values ('that'     , 'the other');
insert into myTable values ('confusing', 'the other');
insert into myTable values ('this'     , 'that'     );
insert into myTable values ('this'     , 'that'     );
insert into myTable values ('that'     , 'confusing');
insert into myTable values ('confusing', 'that'     );
insert into myTable values ('confusing', 'confusing');
insert into myTable values ('that'     , 'the other');
insert into myTable values ('this'     , 'that'     );
insert into myTable values ('confusing', 'the other');

select 
  count(
    case when 
      this = 'that' and 
      that = 'the other'
    then 1
    else null end) "Count 1",
  count(
    case when 
      this = 'this' and 
      that = 'that'
    then 1
    else null end) "Count 2",
  count(
    case when 
      this = 'confusing'
    then 1
    else null end) "Count 3"
from
  myTable;