2

My database has a cost table for items. Items can have more than one cost record.

I want to grab only the items that have multiple cost records.

The UPC field is F01, and the table is called COST_TAB. I only want to grab items where there are multiple entries based on the F01 field.

I'm struggling with how to write this query.

user1837575
  • 307
  • 2
  • 6
  • 14
  • Have a look at http://stackoverflow.com/questions/7151401/sql-query-for-finding-records-where-count-1. That should help. –  Nov 02 '15 at 20:38

4 Answers4

12
select F01
from COST_TAB
group by F01
having count(*) > 1
juergen d
  • 201,996
  • 37
  • 293
  • 362
3

You need to use group by

select f01
from cost_tab
group by f01
having count(f01) > 1
Hogan
  • 69,564
  • 10
  • 76
  • 117
0
select * from COST_TAB
Where COST_TAB.UPC in (select COST_TAB.UPC from COST_TAB group by COST_TAB.UPC having (Count(COST_TAB.UPC) >1) and COST_TAB.UPC = 'F01'
Kentonbmax
  • 938
  • 1
  • 10
  • 16
0

Presumably, you want all the fields in the rows of duplicates (otherwise, a simple aggregation query gets you the F01 values). I would use window functions for this:

select ct.*
from (select ct.*, count(*) over (partition by f01) as cnt
      from cost_tab ct
     ) ct
where cnt > 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • with all due respect... this actual doesn't work. I updated it for ya... ;) +1 – sam yi Nov 02 '15 at 22:52
  • @samyi . . . Your modifications were incorrect. If you don't understand something, ask. – Gordon Linoff Nov 03 '15 at 02:27
  • Please take a look at your original answer. It is not the same as the one you updated to. The original answer, in fact, is incorrect. I would argue that your "new" answer, while it works, is still poorly written (ambiguous table alias). If you want to know why it's incorrect, please ask. ;) – sam yi Nov 03 '15 at 15:14
  • @samyi . . . I added the `ct` alias. You changed other things in the query. – Gordon Linoff Nov 03 '15 at 19:34