Avatar of JDCam
JDCam
 asked on

Oracle 10g Evaluate count from partition

Experts,
I wrote this little query to show me how many unique instances of BREAK I have per ITEM. Output looks good.  I want to add 'WHERE NUM > 1, to show only where there is more than 1 instance.  It's not liking my syntax when I attempt to add this.
Please help.

Select
  ITEM,
  BREAK,
  count(BREAK)OVER(PARTITION BY ITEM) NUM
from (
Select INVT_LEV1 ITEM, INVT_QTY_BKD_FACT BREAK
from C_INVT
where Cust_Code = 'GMAFH'
and COMP_CODE = 'W1'
and ON_HAND_QTY > 0
GROUP BY INVT_LEV1, INVT_QTY_BKD_FACT
order by INVT_LEV1)
where NUM > 1    <----------------- this line is giving error when present !!!!
group by ITEM, BREAK
Oracle DatabaseSQL

Avatar of undefined
Last Comment
JDCam

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
JDCam

ASKER
Perfect .. Thx!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck