Subject Re: [firebird-support] RE: group by and sub-selects
Author Roberto
Post date 2013-12-27T11:01:32Z
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 <anhanguera@...>
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: "avert@..." <avert@...>
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

OWNERNAMELOCATIONRVYEARRV MFG EVENT COUNT
 Our GTC & MARC InventoryOur Warehouse19
 Our GTC & MARC InventoryOur Warehouse200919
 Our GTC & MARC InventoryOur Warehouse200919
 Our GTC & MARC InventoryOur Warehouse200919
 Our GTC & MARC InventoryOur Warehouse200919
 Our GTC & MARC InventoryOur Warehouse201019
 Our GTC & MARC InventoryOur Warehouse201019
 Our GTC & MARC InventoryOur Warehouse201019
 Our GTC & MARC InventoryOur Warehouse201019
 Our GTC & MARC InventoryOur Warehouse201019


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