Talk About Network

Google


Register and Login
Nick
Password
Register create new account Sign up is FREE and you can post replies, new topics, bookmark posts and more!
Recover lost password


Data Bases > Oracle Server > Group bys and s...
Latest [ Topics | Posts ] Archive Post A New Topic Post a Reply
<< Topic < Post Post 1 of 3 Topic 16602 of 17418
Post > Topic >>

Group bys and sub-queries

by dknight.work@[EMAIL PROTECTED] May 22, 2008 at 07:08 AM

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
 




 3 Posts in Topic:
Group bys and sub-queries
dknight.work@[EMAIL PROTE  2008-05-22 07:08:54 
Re: Group bys and sub-queries
Maxim Demenko <mdemenk  2008-05-22 16:41:02 
Re: Group bys and sub-queries
david <dknight.work@[E  2008-05-22 07:58:52 

Post A Reply:
  Go here to Signup

AddThis Feed Button


About - Advertising - Contact - Frequently Asked Questions - Privacy Policy - Terms of Use - Signup

Contact
tan12V112 Sat Nov 22 15:19:51 CST 2008.