Hey All-- Always such a great forum!!
i’ve got a SQL question without using stored procedures this works: select o.ownername, p.location, (select count(1) from reliefd r where r.plantkey=p.uniquekey) RV_Event_count, (select count(1) from cv c where c.plantkey=p.uniquekey and upper(substring( c.manufacturer from 1 for 4))='MINE' ) CV_MINE_count from owners o, plants p where p.ownerkey = o.uniquekey order by rv_event_count desc (returns counts for each owner and plant) BUT I want to do some grouping by too ie. group by year or yyyy/mm (stored as text in the layout of YYYY/MM/DD it's NOT a TimeDate field) select o.ownername, p.location, substring( r.datetested from 1 for 4 ) as RVYear, count(1) as RV_Event_Count from plants p, reliefd r join owners o on p.ownerkey = o.uniquekey where r.plantkey = p.uniquekey and r.datetested=coalesce(r.datetested, '', null, ' ' ) group by o.ownername, p.location, rvyear order by o.ownername this works, Owners, Plants and grouped by year with count in the year of records BUT what i want is the various count-of-rows columns based upon "where" clauses like the first BUT grouped this does NOT run... i cannot do the nested ( select ) in the group by?? ie. what i want select o.ownername, p.location, substring( r.datetested from 1 for 4 ) as RVYear, ( select count(1) from reliefd r where r.plantkey=p.uniquekey and r.manufacturer=’The Manufacturer I want’) RV_Mfg_Event_count from plants p, reliefd r join owners o on p.ownerkey = o.uniquekey where r.plantkey = p.uniquekey and r.datetested=coalesce(r.datetested, '', null, ' ' ) group by o.ownername, p.location, rvyear, RV_Mfg_Event_count order by o.ownername, rv_event_count desc somehow there has to be a way with derived tables? subqueries? or CTE (common table expressions)? any suggestions or things I shuold google? (yes, I've been trying for days to figure this out with google... my SQL isn't very strong) thanks kelly
