| Subject | Re: [firebird-support] group by and sub-selects |
|---|---|
| Author | Roberto |
| Post date | 2013-12-26T19:24:26Z |
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: "avert@..." <avert@...>
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