Hi-- Thanks for the reply.
You've got a handle on what I want to return and the industry. The query I'm still not clear on. I did clean it up a bit and it ran: select x.ownername, x.location, x.RVYear, ( select count(1) from reliefd r where r.plantkey=x.uniquekey ) RV_Mfg_Event_count from ( select o.ownername, p.location, p.uniquekey, substring( r.datetested from 1 for 4 ) as RVYear 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, ' ' ) and RVYear ) x order by x.ownername, x.location, rvyear results OWNERNAME LOCATION RVYEAR RV MFG EVENT COUNT Our GTC & MARC Inventory Our Warehouse 19 Our GTC & MARC Inventory Our Warehouse 2009 19 Our GTC & MARC Inventory Our Warehouse 2009 19 Our GTC & MARC Inventory Our Warehouse 2009 19 Our GTC & MARC Inventory Our Warehouse 2009 19 Our GTC & MARC Inventory Our Warehouse 2010 19 Our GTC & MARC Inventory Our Warehouse 2010 19 Our GTC & MARC Inventory Our Warehouse 2010 19 Our GTC & MARC Inventory Our Warehouse 2010 19 Our GTC & MARC Inventory Our Warehouse 2010 19 So, the counts aren't bound to the "group" by in my original query. I realize I could have a single data grid with an overview grid of owners/plant and counts grouped by year or YYYY/MM. Then, when users scroll that? I could have a results grid in which I do all the counts using the "where" of the row i'm on in the grouped by grid. But i'd like it all in one grid (for export, reports, plotting). The problem seems to be, I want to do aggrigate functions (count) and have each use it's own where clause. BUT i need each count to understand the group it's in. That's where I don't know how to write the select for the count. I can figure it out for one single column but not multiples each with their own where clause. Somehow I think I need a CTE or a derived table for the list of owners/plant and grouped by YYYY or YYYY/MM THEN i need to use that table, each row of it, and join that to the counts being done for each row. I really thankyou for taking time to look at this and your suggestions. regards kelly
