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

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

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

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

Open in new window

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.

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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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