Re: Messed Up Query

Mtek wrote:
> Hi,
>
> I have this query:
>
> SELECT q10.q10_count,
> vi.mgr_flag,vi.inst_type,vi.inst_num,vi.inst_cst,vi.inst_zip,vi.inst_phone,vi.url,vi.turnover,vi.hold_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 can't tell for sure, but it sounds like you just need an analytical function in the outer query...

    count(*) over (partition by vh.ticker)

...or a selective count...

  sum(decode(vh.q1_shares, 0, 1, 0)) over (partition by vh.ticker)

--
Peter