SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Analytics to identify duplicate rows not working as expected

EdStevensJan 27 2017 — edited Feb 2 2017

Oracle 11.2.0.4.0 Std Ed One 64-bit

Oracle Linux 6

Still trying to get my head around analytics. Given this query to identify duplicate rows.

select COMPANY_CODE,

SALES_DIVISION_CODE,

DISCOUNT_CD

count(*)

over (partition by

COMPANY_CODE,

DISCOUNT_CD

) ct

from stage.customer_account

where account_name = 'CONSOLIDATED WIDGETS'

;

If there is duplicated, say 7 times, all seven are listed with a count (ct) of 7. A row that is duplicated 150 times, all 150 are listed, each with a 'ct' value of 150.

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

CO SA PURCHASE_A ACCOUNT_NAME CT

-- -- ---------- ------------------------------------------------------------ ----------

01 01 0010000263 CONSOLIDATED WIDGETS 7

01 01 0010000263 CONSOLIDATED WIDGETS 95

<ETC>

01 01 0010000263 CONSOLIDATED WIDGETS 95

This post has been answered by John Stegeman on Jan 27 2017

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 2 2017

Added on Jan 27 2017

22 comments

3,580 views