Messed Up Query

#21

On 29.04.2008 14:51, Mtek wrote:

Quote:

On Apr 29, 4:18 am, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:
On Apr 28, 7:51 pm, Mtek <m... (AT) mtekusa (DOT) com> wrote:

Hi,
I have this query:
SELECT q10.q10_count,
vi.mgr_flag,vi.inst_type,vi.inst_num,vi.inst_cst,v i.inst_zip,vi.inst_phone,vi.url,vi.turnover,vi.hol d_cnt,
vi.date_,vi.inst_size,vi.inst_dchg,
vh.ticker,vh.q0_shares,vh.q1_shares,vh.q0q1_grwth, vh.q0q1_dchg,vh.shares_out,
mt.comp_name, sd.sector_name, p.closing
FROM vinst vi, vhold vh, master_table mt, stock_data sd, prices p,
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10
WHERE inst_name = 'Yacktman Fund'
AND vi.inst_num = vh.inst_num
AND vh.ticker = mt.ticker
AND mt.m_ticker = sd.m_ticker
AND mt.m_ticker = p.m_ticker;
Take a look at the inner query in the FROM clause:
(SELECT count(*) q10_count FROM vhold vh
WHERE q1_shares = 0 GROUP BY vh.ticker) q10
I basically need the same criteria applied as the outer query. Do I
have to re-list the criteria? I'll have to do that count for 5
fields. It will make this query WAY long.......
I don't see a join with q10. Is a cross join really what you intend?

Well, what I am trying to do is to select a bunch of data, then in the
same query, I want to do a count on a column with it;s own criteria
based on the results of the original select.

Does that make sense?

I'm afraid, no. Maybe I'm too tired right now but "select a bunch of
data" is not very precise. It is especially unclear to me whether you
need the "bunch" only for counting purposes or are interested in the
result as well.

Also, your remark "based on the results of the original select" might
indicate that you have a too procedural view of SQL.

Quote:

Can an OVER help me here? I'm trying to look at it and understand
it.......

Maybe. Can you try to explain more elaborately what you have as input
and expect as output?

Kind regards

robert

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Messed Up Query

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Messed Up Query - 04-30-2008 , 04:42 PM