Re: Query
From: Lennart Jonsson <erik.lennart.jonsson_at_gmail.com>
Date: Mon, 24 Jan 2011 17:11:17 +0100
Message-ID: <ihk8b7$th8$1_at_news.eternal-september.org>
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
>
group by tran_date
order by tran_date
Date: Mon, 24 Jan 2011 17:11:17 +0100
Message-ID: <ihk8b7$th8$1_at_news.eternal-september.org>
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
[...] Received on Mon Jan 24 2011 - 10:11:17 CST