Re: Shorten Query
Date: Tue, 29 Apr 2008 11:32:23 -0700 (PDT)
Message-ID: <3b932ba0-c01b-47d8-9a4c-6a7435b50c55@j22g2000hsf.googlegroups.com>
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