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