title: Oracle sql Count distinct value over partition by?
body: <p>Sorry but I'm confused and can't get this to work.</p>
  
  <p>I want to show the highest quantity of values that are the same value for each item,</p>
  
  <p>What I mean is:-
  I have 70 pallets of an item, Item A
  Each pallet has a unique tag_id
  Each tag_id has a qty on it (qty_on_hand)
  Most of the time this qty_on_hand is the same for a lot of the pallets so for my 70 pallets  I have
  34 tags with a qty_on_hand of 60
  6 tags with a qty_on_hand of 88
  30 tags with a qty_on_hand of 80</p>
  
  <p>I want to show the item number and the number highest number of tags,
  Above example would be Item A 34</p>
  
  <p>But I need to do this for thousands of items in the warehouse.
  I can do a simple 
  select i.qty_on_hand,count(qty_on_hand)
  from inventory i
  where i.zone_1 = 'BULKSTORE'
  and sku_id = '1961834100'
  group by qty_on_hand
  but it's for 1 item (sku_id) and then this still shows me that there are 3 different quantities_on_hand in the bulk store wereas I only want the highest for each sku.</p>
  
  <p>I've looked at count distinct partition by etc, etc, 
   but not been able to get it to work so any working examples would be great.</p>
  
  <p>Hopefully I've not confused you too much.
  Thanks in advance
  Dean</p>