| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
07-16-09, 07:30
|
|
Registered User
|
|
Join Date: Jul 2009
Posts: 5
|
|
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
|
|
07-16-09, 09:37
|
|
Lead Application Develope
|
|
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,222
|
|
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. 
|
|
07-16-09, 10:30
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,415
|
|
|
|
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.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
ora-00979 not a GROUP BY expression - help
