CODEWITHSUNDEEP
sqloracle-database
Monomoni
Monomoni

Reputation: 435

sql oracle: Listing Records that has a value repeated more than N number of times within the same month

I am suppose to fetch from my records, if there is more than three records with the same Tradename stored within the same month.

An example of the record I have

TradeID      Tradename           PrescDT
------------------------------------------
1109         Panadol             sysdate-1
1123         Opioids             sysdate-3
1125         Doxycycline         sysdate-3
1138         Panadol             sysdate-14
1139         Panadol             sysdate-3
1141         Panadol             sysdate-5
1142         Codeine             sysdate-5
1162         Opioids             sysdate-14
1167         Codeine             sysdate-14
1168         Prednisone          sysdate-14
1170         Codeine             sysdate-21
1172         Codeine             sysdate-22

so it should return me with only this, where I only want the tradename and date

Tradename           PrescDT
------------------------------
Panadol             sysdate-1
Panadol             sysdate-14
Panadol             sysdate-3
Panadol             sysdate-5

I have tried using this but the returns me with all the records repeating at least thrice within the same month and it's not what I'm looking for

SELECT Tradename, PrescDT
FROM (
    SELECT p.*, COUNT(*) OVER (PARTITION BY TRUNC(PrescDT, 'MM')) AS c 
    FROM PRESCRIPTION p
    ) p
WHERE c > 3
ORDER BY prescDT ASC;

Upvotes: 0

Views: 72

Answers (1)

cha
cha

Reputation: 10411

Your query is almost right. You need to include the tradename into the PARTITION BY clause:

SELECT Tradename, PrescDT
FROM (
    SELECT p.*, COUNT(*) OVER (PARTITION BY Tradename, TRUNC(PrescDT, 'MM')) AS c 
    FROM PRESCRIPTION p
    ) p
WHERE c > 3
ORDER BY prescDT ASC;

Upvotes: 3

Related Questions