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
