Count using Analytic Function

I need a sql analytic function to do the count as follows: Below is the sample data. The payments for the accounts in a month should be counted. If the payments are on the same date then it should be counted only once.
For example the account 0000026368 appears twice in december but on the same day. so it should be counted only once. And the same account appears thrice in jan. But is should be counted only two times as it appears only on 2 different days one for 1/1 and one for 1/15. (If the account has payment twice on the same day it will have same pay_event_id). I need a SQL analytic function for count.

Hope I am clear.

acct                    pay_dt             Pay_event              pay_amt         count
0000000190      1/23/2008        722656429643       234                 2
0000000190      1/3/2008          368313739069        243                2
0000010000      1/3/2008         756189483296        4564               1
0000010000      12/7/2007       484796969455       132                  1
0000026368      12/7/2007       444318277828        258.61            1
0000026368      12/7/2007       444318277828        5435                1
0000026368       1/1/2008        000002660891        212                  2
0000026368        1/1/2008       000002660891         23                    2
0000026368        1/15/2008     432847923803         10                    2
LVL 13
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Try this..
select acct, pay_dt, pay_event, count(DISTINCT pay_event) over (partition by to_char(pay_dt,'MM')) as CNT from TABLE

SELECT COUNT(*), acct, TO_CHAR( pay_dt, 'mm/yyyy')  FROM
(SELECT acct, pay_dt FROM sample_data A
 WHERE rowid > (SELECT min(rowid) FROM sample_data B
                            WHERE A.acct = B.acct
                            AND A.pay_dt = B.pay_dt)
GROUP BY acct, TO_CHAR(pay_dt, 'mm/yyyy')
ORDER BY acct, TO_CHAR( pay_dt, 'mm/yyyy');
i'll take mine back.. it does not work

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SujithData ArchitectCommented:
Use this query, change the table name to your table name.
select tbl1.acct, pay_dt, pay_event, pay_amt,
from tbl1, 
(select acct, to_char(pay_dt, 'YYYYMM') pay_mth, count(distinct trunc(pay_dt)) cn
from tbl1
group by acct, to_char(pay_dt, 'YYYYMM')) X
where tbl1.acct = X.acct 
and to_char(pay_dt, 'YYYYMM') = X.pay_mth

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jinesh KamdarCommented:
@crgary_tx: I don't see "analytic" functions being used in the accepted solution ??
crgary_txAuthor Commented:
The query didnt perform bad even for millions of rows though I was hoping to see how it could be written using analytic function.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.