Re: Group bys and sub-queries

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 22 May 2008 16:41:02 +0200
Message-ID: <483585FE.7070205@gmail.com>

dknight.work_at_googlemail.com schrieb:
> 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

SQL> create table parent (

   2 parent_id number not null,
   3 class varchar2(1)
   4 );
SQL>
SQL> create table child (

   2 child_id number not null,
   3 parent_id number not null,
   4 date_done date
   5 );

SQL>
SQL> insert into parent(parent_id,class) values(1,'A');
SQL> insert into parent(parent_id,class) values(2,'A');
SQL> insert into parent(parent_id,class) values(3,'A');
SQL> insert into parent(parent_id,class) values(4,'A');
SQL> insert into parent(parent_id,class) values(5,'A');
SQL> insert into parent(parent_id,class) values(6,'B');
SQL> insert into parent(parent_id,class) values(7,'B');
SQL>
SQL>
SQL> insert into child(child_id,parent_id,date_done) values(1,1,date 

'2007-04-30');
SQL> insert into child(child_id,parent_id,date_done) values(2,1,date '2007-05-14');
SQL> insert into child(child_id,parent_id,date_done) values(3,1,date '2007-05-15');
SQL> insert into child(child_id,parent_id,date_done) values(4,1,date '2007-05-16');
SQL> insert into child(child_id,parent_id,date_done) values(5,2,date '2007-05-21');
SQL> insert into child(child_id,parent_id,date_done) values(6,2,date '2007-05-21');
SQL> insert into child(child_id,parent_id,date_done) values(7,3,date '2007-05-22');
SQL> insert into child(child_id,parent_id,date_done) values(8,3,date '2007-05-22');
SQL> insert into child(child_id,parent_id,date_done) values(9,3,date '2007-05-22');
SQL> insert into child(child_id,parent_id,date_done) values(10,3,date '2007-05-23');
SQL> insert into child(child_id,parent_id,date_done) values(11,3,date '2007-05-23');
SQL> insert into child(child_id,parent_id,date_done) values(12,4,date '2007-05-24');
SQL> insert into child(child_id,parent_id,date_done) values(13,4,date '2007-05-24');
SQL> insert into child(child_id,parent_id,date_done) values(15,6,date '2007-05-17');
SQL>
SQL> select

   2 round(date_done,'IW') week_commencing,    3 nullif(count(case when class='A' then class end),0) a,    4 nullif(count(case when class='B' then class end),0) b    5 from parent p,child c
   6 where p.parent_id=c.parent_id
   7 group by round(date_done,'IW')
   8 order by week_commencing;

WEEK_COMME A B
---------- ---------- ----------

30/04/2007          1
14/05/2007          3          1
21/05/2007          9

Best regards

Maxim Received on Thu May 22 2008 - 09:41:02 CDT