Kelly, If I understood what you want, you wanna do something like a decision cube.
I would take your query and change it to... select x.ownername, x.location, x.RVYear, x.RVMonth, ( select count(1) from reliefd r where r.plantkey=x.uniquekey and r.manufacturer=’The Manufacturer I want’) RV_Mfg_Event_count from ( select o.ownername, p.location, p.uniquekey, substring( r.datetested from 1 for 4 ) as RVYear, substring( r.datetested from 1 for 7 ) as RVMonth, 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, ' ' ) ) x order by o.ownername, p.location, rvyear With x.ownername and x.location, you have one axis of your cube. And with RVYear and x.RVMonth you get the time axis. Probably, you will need some more binding between your count subquery and the other one (its where clause). The way you will get this data, you apply to some front-end controls, like some grids, or to a Excel worksheet, and you can have a drill-down for your data, in two axis. From this, you can build a 3D graphic, or dinamically see your data, according to the attention point where you want to expand your data. Excel or these kind of controls will by themselves aggregate the data acording to their affiliation, according to the data axis. If I am wrong about your intentions, excuse me. But I see you building a solution for industry maintenance, where I worked years ago. And also I worked a little with DataWarehouse, and I believe your solution has both pratical connections. You can also run counts on the many possible combinations of data, but I don´t see them very usefull for you. But, may be another one has other point of view than mine. Good luck, Roberto Camargo. ________________________________ From: "av...@telusplanet.net" <av...@telusplanet.net> To: firebird-support@yahoogroups.com Sent: Tuesday, December 24, 2013 8:39 PM Subject: [firebird-support] group by and sub-selects Hey All-- Always such a great forum!! i’ve got a SQL question without using stored procedures this works: select o.ownername, p.location, (select count(1) from reliefd r where r.plantkey=p.uniquekey) RV_Event_count, (select count(1) from cv c where c.plantkey=p.uniquekey and upper(substring( c.manufacturer from 1 for 4))='MINE' ) CV_MINE_count from owners o, plants p where p.ownerkey = o.uniquekey order by rv_event_count desc (returns counts for each owner and plant) BUT I want to do some grouping by too ie. group by year or yyyy/mm (stored as text in the layout of YYYY/MM/DD it's NOT a TimeDate field) select o.ownername, p.location, substring( r.datetested from 1 for 4 ) as RVYear, count(1) as RV_Event_Count 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, ' ' ) group by o.ownername, p.location, rvyear order by o.ownername this works, Owners, Plants and grouped by year with count in the year of records BUT what i want is the various count-of-rows columns based upon "where" clauses like the first BUT grouped this does NOT run... i cannot do the nested ( select ) in the group by?? ie. what i want select o.ownername, p.location, substring( r.datetested from 1 for 4 ) as RVYear, ( select count(1) from reliefd r where r.plantkey=p.uniquekey and r.manufacturer=’The Manufacturer I want’) RV_Mfg_Event_count 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, ' ' ) group by o.ownername, p.location, rvyear, RV_Mfg_Event_count order by o.ownername, rv_event_count desc somehow there has to be a way with derived tables? subqueries? or CTE (common table expressions)? any suggestions or things I shuold google? (yes, I've been trying for days to figure this out with google... my SQL isn't very strong) thanks kelly
