Group bys and sub-queries

[dknigh...@googlemail.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

[Maxim Demenko]

dknigh...@googlemail.com schrieb:

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

[david]

On 22 May, 15:41, Maxim Demenko <mdeme...@gmail.com> wrote:
> dknight.w...@googlemail.com schrieb:
>
<snip>

That seems to have done the trick. Many thanks

- David