Kelly, I forgot something. If agree to send the data, make use of IBPhoenix ODBC driver to connect Firebird to MS Access to export your data (and the tables model).
After having exported the envolved tables, inside MS Access db, connect them to each other as they are in your db (I hope your db has referencial integrity). Then, the data will be very usefull to make this analisys. Roberto Camargo. ________________________________ From: Roberto <anhangu...@yahoo.com> To: "firebird-support@yahoogroups.com" <firebird-support@yahoogroups.com> Sent: Friday, December 27, 2013 6:57 AM Subject: Re: [firebird-support] RE: group by and sub-selects Kelly, As the counts shows - and I previewed this - you still have to do some bindings more to the count subquery. Can be of help if you send the structure of the tables involved in this query, to see how the tables are linked between them. Still better can be if you send a MS Access db having the same structure and a little amount of data. Then, one can play with the data and have more conditions to find the solution you need. If possible. The way I think the solution, one will not only scroll trough the data, but also contract the tuples of no interest, and expand those where one think to better analyse. Having data as I told, it´s possible to do that. And all the solution build using MS Access (you can bind your FB db to MS Access db and run such solution, without having to export data). And you don´t need extra tables. Some views may help to divide the problem for better understanding. Regards, Roberto Camargo. ________________________________ From: "av...@telusplanet.net" <av...@telusplanet.net> To: firebird-support@yahoogroups.com Sent: Thursday, December 26, 2013 7:24 PM Subject: [firebird-support] RE: group by and sub-selects 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
