Date: Mon, 24 Jan 2011 07:50:09 -0800 (PST)
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! Received on Mon Jan 24 2011 - 09:50:09 CST