Kelly, you want a few changes to clean up your SQL, it is bad practise to mix
implicit and explicit joins in the same query and your comparison wth COALESCE
will always be true (coalesce(r.datetested...) will return r.datetested unless
r.datetested is null and since you on the next line requires this column to be
> '2010', the coalesce part can simply be removed.
What you want may be something like:
select o.ownername, p.location, 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(c.mostrecent='T', 1, 0)) cV_event_count,
sum(iif(upper(r.manufacturer) starting 'MASO', 1, 0)) CV_mason_count
from plants p
join reliefd r on p.uniquekey = r.plantkey
join owners o on p.ownerkey = o.uniquekey
where r.datetested > '2010'
group by o.ownername, p.location, rvyear
order by 4 desc, o.ownername
but your detail query doesn't involve any reference to datetested, so I'm
basically just guessing...
If this is still not what you want, please explain how you want datetested to
be part of your detail query (it is not in your subselects, and Firebird is
even worse than me at guessing).
HTH,
Set
