Re: Group bys and sub-queries

On 22 May, 15:41, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> dknight.w..._at_googlemail.com schrieb:
>

<snip>

> 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

That seems to have done the trick. Many thanks

• David