Hi Sevin--

WONDEFUL!!  thank-you.

i'm by no means a SQL coder so, i love the advice on implicit joins.  and your code is far more readable too.

your trick of "sum( iff( ) )" as a column works exactly how i want.  genius.  

i knew i needed to join my results query on the group-by year query.  just didn't know how to write the counters... the sum( if() ) does it.

i have multiple tables i want to perform this on and realize now that i can't combine them all; it will be one query per table that as the counts.  but that makes perfect sense to me now too.

and the coalesce() i realized the where date>2010 and the coalesce() would cancel out eachother but what i wanted to show was, i need to make sure a date of null or '' were grouped together vs. having 2 groups; one for null, one for ''

i figured out i was using coalesce() all wrong.  it needed to be in the select NOT the where clause.

(I messed with your SQL and still got 2 groups or wrong counts... so, i googled coalesce)

this is great.  the sum() doesn't need to be in the group by and i can group by more if i want to.

thanks.  o owe you a beer.

my new SQL:  now I can do things in the where such as ">=2013" to get year-to-date results.

select o.ownername, p.location, coalesce(substring(r.datetested from 1 for 4), '') as RVYear, count(1) as RV_Event_Count, 

sum(iif(upper(r.manufacturer) starting 'CONS' or upper(r.manufacturer) starting 'DRES', 1, 0)) RV_Cons_count,

sum(iif(upper(r.NewValveEntry) ='T' or upper(r.MaintFor) containing 'NEW' or upper(r.status) containing 'NEW', 1, 0)) RV_New_count

from plants p

join reliefd r on p.uniquekey = r.plantkey

join owners o on p.ownerkey = o.uniquekey

//where

//r.datetested > '2010' 

//r.datetested=coalesce(  r.datetested, '' )

group by o.ownername, p.location, rvyear

//order by 3 desc, o.ownername

order by o.ownername