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.......

Thanks!

John

Mtek 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 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

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?

robert

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?
>
> robert


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?

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

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?
>
> robert


Well, I want to select a certain criteria from X number of tables. I
also want a COUNT of certain columns with their own critera. And, I'm
trying to put this all into one query.......

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