A Better Way for Conditional Counting?

Question

Imagine I have a CTE that creates a result set containing all of the information I need and need to do a bunch of conditional counting on the result set. Is there a better way to do it than a bunch of subqueries?

I can't use count() over () either as I need to sometimes do a distinct count on values and using a case when val=true then 1 else null end to conditionally count doesn't let me distinctly count, not to mention that it is basically the same as doing a bunch of subqueries.

Any recommendations, or is creating a bunch of subqueries the way to go?

(example SQL Fiddle)

Table Definitions

create table person (id int, name varchar2(20), age int, cityID int);
create table city (id int, name varchar2(20), stateID int);
create table state (id int, name varchar2(20));

insert into person values(1, 'Bob', 45, 1);
insert into person values(2, 'Joe', 33, 1);
insert into person values(3, 'Craig', 20, 1);
insert into person values(4, 'Alex', 45, 2);
insert into person values(5, 'Kevin', 33, 3);

insert into city values(1, 'Chicago', 1);
insert into city values(2, 'New York', 2);
insert into city values(3, 'Los Angeles', 3);

insert into state values(1, 'Illinois');
insert into state values(2, 'New York');
insert into state values(3, 'California');

SQL Query Example

with cte as (
  select p.name pName
    , p.age pAge
    , c.name cName
    , s.name sName
  from person p
  inner join city c
    on p.cityID = c.ID
  inner join state s
    on c.stateID = s.ID
)
select distinct
    (select count(*) from cte) totalRows
  , (select count(*) from cte where pAge = 45) total45YO
  , (select count(*) from cte where cName like 'Chicago') totalChicago
  , (select count(distinct cName) from cte) totalCities
 from cte

An example output I would hope for

 TOTALROWS  TOTAL45YO   TOTALCHICAGO    TOTALCITIES
------------------------------------------------------
     5         2             3               3

Answer 1

Easiest is just as @jarlh mentions and use case/sum combinations to accomplish as follows.

SQL> select count(*) totalRows
  2    , sum(case when p.age=45 then 1 else 0 end) total45YO
  3    , sum(case when c.name like 'Chicago' then 1 else 0 end) totalChicago
  4    , count(distinct c.name) totalCities
  5  from person p
  6  inner join city c
  7    on p.cityID = c.ID
  8  inner join state s
  9    on c.stateID = s.ID;
TOTALROWS    TOTAL45YO    TOTALCHICAGO    TOTALCITIES
____________ ____________ _______________ ______________
           5            2               3              3


SQL>