From: The Magnet <art_at_unsu.com>
Date: Mon, 24 Jan 2011 07:50:09 -0800 (PST)
Message-ID: <f1c1494d-828b-4900-a971-e635ae2c2ca9_at_e20g2000vbn.googlegroups.com>


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

Original text of this message