| Subject | Re: [firebird-support] RE: group by and sub-selects |
|---|---|
| Author | Roberto |
| Post date | 2013-12-27T09:57:17Z |
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
| 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