Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    5

    Angry Unanswered: ora-00979 not a GROUP BY expression - help

    select to_char(t.cross_date, 'YYYY') year,t.cross_prefix, min(t.cross_date) start_date,
    max(t.cross_date) end_date, count(*) "No_of_crosses",
    (select sum(mean_value) from cross_means cm where t.cross_prefix = cm.cross_prefix and cm.measure_type = 'GERM') "Germination Count",
    (select count(*) from cross c where c.cross_prefix=t.cross_prefix and to_char(c.cross_date,'YYYY') between 2000 and 2009 and c.cross_type = 'BIPAR' and TO_CHAR(c.cross_date, 'YYYY')= TO_CHAR(t.cross_date,'YYYY')) bipar,
    (select count(*) from cross c where c.cross_prefix=t.cross_prefix and to_char(c.cross_date,'YYYY') between 2000 and 2009 and c.cross_type = 'COMPOSIT') composit,
    (select count(*) from cross c where c.cross_prefix=t.cross_prefix and to_char(c.cross_date,'YYYY') between 2000 and 2009 and c.cross_type = 'MO') mo,
    (select count(*) from cross c where c.cross_prefix=t.cross_prefix and to_char(c.cross_date,'YYYY') between 2000 and 2009 and c.cross_type = 'MP') mp,
    (select count(*) from cross c where c.cross_prefix=t.cross_prefix and to_char(c.cross_date,'YYYY') between 2000 and 2009 and c.cross_type = 'SELFS' ) selfs,
    (select count(*) from cross c where c.cross_prefix=t.cross_prefix and to_char(c.cross_date,'YYYY') between 2000 and 2009 and c.avg_stain < 30 ) "<30",
    round((select count(*) from cross c where c.cross_prefix=t.cross_prefix and to_char(c.cross_date,'YYYY') between 2000 and 2009 and c.avg_stain < 30 )/
    (select count(*) from cross c where c.cross_prefix=t.cross_prefix and to_char(c.cross_date,'YYYY') between 2000 and 2009)*100,2) "%<30"
    from cross t
    where to_char(t.cross_date,'YYYY') between 2000 and 2009
    and t.cross_type in('BIPAR','MO','MP','SELFS','COMPOSIT')
    group by to_char(t.cross_date,'YYYY'), t.cross_prefix

    why does this query give me the ora-00979 error o

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    because you are using max(t.cross_date) and min(t.cross_date) in the select and that means everything else must be in a group by.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT   To_char(t.cross_date,'YYYY') YEAR,
             t.cross_prefix,
             Min(t.cross_date)            start_date,
             Max(t.cross_date)            end_date,
             Count(* )                    "No_of_crosses",
             (SELECT Sum(mean_value)
              FROM   cross_means cm
              WHERE  t.cross_prefix = cm.cross_prefix
                     AND cm.measure_type = 'GERM') "Germination Count",
             (SELECT Count(* )
              FROM   cross c
              WHERE  c.cross_prefix = t.cross_prefix
                     AND To_char(c.cross_date,'YYYY') BETWEEN 2000 AND 2009
                     AND c.cross_type = 'BIPAR'
                     AND To_char(c.cross_date,'YYYY') = To_char(t.cross_date,'YYYY')) bipar,
             (SELECT Count(* )
              FROM   cross c
              WHERE  c.cross_prefix = t.cross_prefix
                     AND To_char(c.cross_date,'YYYY') BETWEEN 2000 AND 2009
                     AND c.cross_type = 'COMPOSIT') composit,
             (SELECT Count(* )
              FROM   cross c
              WHERE  c.cross_prefix = t.cross_prefix
                     AND To_char(c.cross_date,'YYYY') BETWEEN 2000 AND 2009
                     AND c.cross_type = 'MO') mo,
             (SELECT Count(* )
              FROM   cross c
              WHERE  c.cross_prefix = t.cross_prefix
                     AND To_char(c.cross_date,'YYYY') BETWEEN 2000 AND 2009
                     AND c.cross_type = 'MP') mp,
             (SELECT Count(* )
              FROM   cross c
              WHERE  c.cross_prefix = t.cross_prefix
                     AND To_char(c.cross_date,'YYYY') BETWEEN 2000 AND 2009
                     AND c.cross_type = 'SELFS') selfs,
             (SELECT Count(* )
              FROM   cross c
              WHERE  c.cross_prefix = t.cross_prefix
                     AND To_char(c.cross_date,'YYYY') BETWEEN 2000 AND 2009
                     AND c.avg_stain < 30) "<30",
             Round((SELECT Count(* )
                    FROM   cross c
                    WHERE  c.cross_prefix = t.cross_prefix
                           AND To_char(c.cross_date,'YYYY') BETWEEN 2000 AND 2009
                           AND c.avg_stain < 30) / (SELECT Count(* )
                                                    FROM   cross c
                                                    WHERE  c.cross_prefix = t.cross_prefix
                                                           AND To_char(c.cross_date,'YYYY') BETWEEN 2000 AND 2009) * 100,
                   2) "%<30"
    FROM     cross t
    WHERE    To_char(t.cross_date,'YYYY') BETWEEN 2000 AND 2009
             AND t.cross_type IN ('BIPAR','MO','MP','SELFS',
                                  'COMPOSIT')
    GROUP BY To_char(t.cross_date,'YYYY'),
             t.cross_prefix
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •