On 29.04.2008 14:51, Mtek wrote:
> On Apr 29, 4:18 am, Robert Klemme <shortcut...@googlemail.com> wrote:
>> On Apr 28, 7:51 pm, Mtek <m...@mtekusa.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.

> 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