Re: Query

From: The Magnet <art_at_unsu.com>
Date: Mon, 24 Jan 2011 11:41:18 -0800 (PST)
Message-ID: <d7cc864a-8e36-4d92-9a7c-56a2cc008f37_at_i18g2000yqn.googlegroups.com>

On Jan 24, 10:11 am, Lennart Jonsson <erik.lennart.jons..._at_gmail.com> wrote:
> On 2011-01-24 16:50, The Magnet wrote:
>
>
>
> > Hi,
>
> > I need to create a very strange query.  I'm sure it can be done, maybe
> > with analytical functions or something, but I need help.  We are on
> > 10g R2.
>
> > Say I have some data like this:
>
> > ID                                TRAN_DATE
> > ABC                             1/1/2010
> > DEF                             1/1/2010
> > GHI                               2/3/2010
> > V.JKI                            2/3/2010
>
> > Now, what I want is a total count of the record set AND a count to all
> > ID's that start with 'V.'.  But, other criteria is applied to the
> > entire query.
>
> > SELECT COUNT(*) OVER () total, ...........
> > WHERE tran_date > '01-DEC-09';
>
> > SPLIT DATE                 TOTAL             COUNT V.
> > 1/1/2010                        2                      0
> > 2/3/2010                        2                      1
>
> Perhaps something like:
>
> select tran_date as split_date,
>        count(1) as total,
>        count(case when id like 'V%' then 1 end) as count_v
> from ...
> group by tran_date
> order by tran_date
>
> will do?
>
> /Lennart
>
> [...]

Works perfectly! Thanks! Always forgot about that CASE statement..... Received on Mon Jan 24 2011 - 13:41:18 CST