Re: Shorten Query

From: Urs Metzger <urs_at_ursmetzger.de>
Date: Tue, 29 Apr 2008 21:07:37 +0200
Message-ID: <fv7rlc$sor$1@online.de>

Mtek schrieb:

> On Apr 29, 12:51 pm, Urs Metzger <u..._at_ursmetzger.de> wrote:

>> Mtek schrieb:
>>
>>> Hi,
>>> Please look at this query. notice that most of the criteria is the
>>> same, except for 1 line. I need to get the count for 10 items. Is
>>> there a better way to do this so I do not have to repeat the criteria
>>> for each item I want to return?
>>> I realize that one line of the criteria is different, but I am hoping
>>> to not have to repeat the other criteria.....
>>> SELECT a.q1, b.q2
>>> FROM (SELECT count(*) q1
>>> FROM vinst vi, vhold vh, master_table mt, stock_data sd, prices
>>> p
>>> WHERE 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
>>> AND q1_shares = 0) a,
>>> (SELECT count(*) q2
>>> FROM vinst vi, vhold vh, master_table mt, stock_data sd, prices
>>> p
>>> WHERE 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
>>> AND q0_shares = 0) b;
>>> Thank you much,
>>> John
>> SELECT SUM(DECODE(q1_shares, 0, 1, 0)) as q1,
>> SUM(DECODE(q0_shares, 0, 1, 0)) as q2
>> FROM vinst vi, vhold vh, master_table mt, stock_data sd, prices p
>> WHERE 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;
>>
>> hth,
>> Urs Metzger

> 
> 
> Your solution is interesting.  In fact, I like it.  Is this possible
> also if I need to use a > or <?
> 
> The actual results they are looking for is:
> 
> -- A count of the number of tickers that have Q1_SHARES = 0
> -- A count of the number of tickers that have Q0_SHARES = 0
> -- A count of the number of tickers that have Q1_SHARES > Q0_SHARES
> -- A count of the number of tickers that have Q1_SHARES < Q0_SHARES
> -- A count of the number of tickers that have Q1_SHARES = Q0_SHARES
> 

You can use CASE rather than DECODE.

Urs Received on Tue Apr 29 2008 - 14:07:37 CDT