It always bothers me when I have to kluge a query to get it to work.

I have a table of questionnaires and the person who completed each one.

create table ques_set (ques_set_id int, pers_id int);

insert into ques_set values (1,48);
insert into ques_set values (2,93);
insert into ques_set values (3,64);
insert into ques_set values (4,72);
insert into ques_set values (5,48);
insert into ques_set values (6,72);
insert into ques_set values (7,56);
insert into ques_set values (8,64);
insert into ques_set values (9,81);
insert into ques_set values (10,72);
commit;

From this, I would like to know: how many people created questionnaires; how many people created 1 questionnaire, 2 questionnaires, etc.; how many total questionnaire uses that translates to; and grand totals for people and uses.

Like so:

USE_PER_PERS   PERS   USE
           1      3     3   -- 3 people have created 1 questionnaire for a
                            --    total of 3 questionnaires
           2      2     4   -- 2 people have created 2 questionnaires for a
                            --    total of 4 questionnaires
           3      1     3   -- 1 person has created 3 questionnaires for a
                            --    total of 3 questionnaires
       Total      6    10   -- 6 people have created a total of 10
                            --    questionnaires

A couple of your postings really helped me formulate this query and it works. However, when I get to that last step (working from the innermost sub-query out - step(5) in my comments), I have to place a bogus aggregate function on the 'pers' and 'use' columns in order to do the final 'group by'. I used 'sum' but 'min', 'max', 'avg', etc. would also work.

-- (5) group the aggregates to get 'Total' into one rec instead of two
select
   use_per_pers,
   sum(pers) as pers,
   sum(use) as use
from
   (
   -- (4) identify the sums for num of persons and num of uses
   select
      nvl2(use_per_pers,to_char(lpad(use_per_pers,12)),lpad('Total',12))
         as use_per_pers,
      pers,
      use
   from
      (
      -- (3) calc the total number of uses
      select
         use_per_pers,
         pers,
         sum(use_per_pers*pers) as use
      from
         (
         -- (2) the number of people who have 1, 2, 3, etc. ques sets
         select
            use_per_pers,
            count(pers_id) pers
         from
            (
            -- (1) the number of ques sets for each pers
            select
               pers_id,
               count(*) use_per_pers
            from ques_set
            group by pers_id
            )
         group by rollup(use_per_pers)
         )
      group by grouping sets ((),(use_per_pers,pers))
      )
   )
group by use_per_pers
order by use_per_pers
/

Am I being too anal or is there a better way?

Thanks.

Brian

ops$tkyte%ORA10GR2> select decode( grouping(cnt), 0, to_char(cnt,'999,999'), 'Total:' ) use_per_pers,
  2         count(*) pers,
  3             decode( grouping(cnt), 0, count(*)*cnt, sum(cnt) ) use
  4    from (
  5  select pers_id, count(*) cnt
  6    from ques_set
  7   group by pers_id
  8         )
  9   group by rollup( cnt )
 10   order by cnt
 11  /

USE_PER_       PERS        USE
-------- ---------- ----------
       1          3          3
       2          2          4
       3          1          3
Total:            6         10