Re: Messed Up Query
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Wed, 30 Apr 2008 23:42:43 +0200
Message-ID: <67s7elF2p0j14U1@mid.individual.net>
>> On Apr 28, 7:51 pm, Mtek <m..._at_mtekusa.com> 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 don't see a join with q10. Is a cross join really what you intend?
Date: Wed, 30 Apr 2008 23:42:43 +0200
Message-ID: <67s7elF2p0j14U1@mid.individual.net>
On 29.04.2008 14:51, Mtek wrote:
> On Apr 29, 4:18 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
>> On Apr 28, 7:51 pm, Mtek <m..._at_mtekusa.com> 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 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 Received on Wed Apr 30 2008 - 16:42:43 CDT