Group bys and sub-queries

From: <dknight.work_at_googlemail.com>
Date: Thu, 22 May 2008 07:08:54 -0700 (PDT)
Message-ID: <acf04e64-6e10-4748-982f-cd413a386444@e53g2000hsa.googlegroups.com>

Hi

I have the following two table structures:

create table parent (
  parent_id number not null,
  class varchar2(1)
);

create table child (
  child_id number not null,
  parent_id number not null,
  date_done
);

I want to retrieve the number of child records for each of class A and class B of the parent records, grouped by date_done. E.G. -

week_commencing	A	B
30/04/2007	1
14/05/2007	3	1
21/05/2007	9

I am attempting to perform the following query:

select round(overall_child.date_done,'IW') week_commencing,

       (select count(subset1_child.parent_id)
        from   parent subset1_parent,
               child subset1_child
        where  overall_child.child_id = subset1_child.child_id
        and    subset1_parent.parent_id = subset1_child.parent_id
        and    subset1_parent.class = 'A'
       ) A,
       (select count(subset2_child.parent_id)
        from   parent subset2_parent,
               child subset2_child
        where  overall_child.child_id = subset2_child.child_id
        and    subset2_parent.parent_id = subset2_child.parent_id
        and    subset2_parent.class = 'B'
       ) B
from   child overall_child,
       parent overall_parent

where overall_parent.parent_id = overall_child.parent_id group by round(overall_child.date_done,'IW') order by week_commencing;

Attempting to run this results in the error:

   5         where  overall_child.child_id = subset1_child.child_id
                    *

ORA-00979: not a GROUP BY expression

Adding overall_child.child_id to the "group by" clause allows the query to run, but gives one row per record where I need one row covering each week.

Does anyone have any suggestions as to how I can achieve this aim?

Many thanks

David Received on Thu May 22 2008 - 09:08:54 CDT