• Status: Solved
• Priority: Medium
• Security: Public
• Views: 964

# 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.
TIA,
Gary

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
0
crgary_tx
1 Solution

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

0

Commented:
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');
0

Commented:
i'll take mine back.. it does not work

sorry...
0

Commented:
Use this query, change the table name to your table name.
``````select tbl1.acct, pay_dt, pay_event, pay_amt, X.cn
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
/
``````
0

Commented:
@crgary_tx: I don't see "analytic" functions being used in the accepted solution ??
0

Author 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.

Thanks,
Gary
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.