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



So, I applying a set of criteria to the entire result set. Then,
within that, I want a count of the total record set, plus another
column that tells me the count of ID's that start with 'V.' from
within the same record set that had the date criteria applied.

I am hoping to only issue the date criteria once. Inline tables,
probably, analytical functions, probably, but, 2 sets of criteria to
the same result set. Seems a bit impossible.

This query will create a materialized view.......

Hope this all makes sense.

Thanks!

On 2011-01-24 16:50, The Magnet wrote:
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

[...]

On Jan 24, 10:11*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Works perfectly! Thanks! Always forgot about that CASE
statement.....