CODEWITHSUNDEEP
sql
Zajac
Zajac

Reputation: 5

Group by column Having COUNT (column) >1 in a single column

  1. List item

I am attempting to join two tables filtering out an item_id that has a unique value, and displaying any item_id that contains the same value. The SQL statement I am using seems to half work except it returns everything I thought I had this figure out but I guess not.

This statement pulls everything. 

--join two tables filtering out and item_id that has a unique value and displaying any 
--item_id that contains the same value

SELECT inv_bin.bin, inv_bin.quantity, inv_bin.inv_bin_uid, inv_bin.inv_mast_uid,
       inv_mast.inv_mast_uid,inv_mast.item_desc, inv_mast.item_id
FROM   inv_bin left join inv_mast on inv_bin.inv_mast_uid = inv_mast.inv_mast_uid  
WHERE inv_mast.item_id in ( SELECT item_id from inv_mast
                            GROUP BY item_id HAVING COUNT (item_id) >= 1 ) 
      AND inv_bin.bin not like 'rec'
      AND inv_bin.bin not like 'DEFBIN'
      AND inv_bin.bin not like 'DEFAULTBIN'
ORDER BY inv_mast.item_id;

However if i remove the '=' from the Group By item_id Having COUNT (item_id) >= 1 ) then the query returns nothing. I know i have data in the item_id column that is the same:

bin        item_id
07C-C15    002-09-121
Z07-OS     002-09-121

Anyway can some one tell me where i am going wrong with this it seem to me that useing > 1 would display every thing in item_id with the same value.

Thanks, Brett

bin         quantity   inv_bin_uid   inv_mast_uid   inv_mast_uid   item_id
07C-C15     0          135           70             70             002-09-121
Z07-OS      10         130277        70             70             002-09-121
04C-B21     0          354           289            289            032-36-26
04C-B04     0          356           291            291            032-38-26
02A-B01     2          101           48             48             5-40050720L  
Z29-SKID00  0          117           48             48             5-40050720L

Here is the finished statement that produces the desired reults. 

/*join two tables filtering out and item_id that has a unique value and displaying any 
item_id that contains the same value */

SELECT inv_bin.bin, inv_bin.quantity, inv_bin.inv_bin_uid, inv_bin.inv_mast_uid,
inv_mast.item_desc, inv_mast.item_id

from inv_bin left join inv_mast on inv_bin.inv_mast_uid = inv_mast.inv_mast_uid  

where inv_bin.inv_mast_uid in ( 
SELECT inv_mast_uid FROM inv_bin  
WHERE inv_bin.bin NOT IN ('REC','DEFBIN','DEFAULTBIN')
GROUP BY inv_mast_uid HAVING COUNT(inv_mast_uid)>1 )

and inv_bin.bin not like 'rec'
and inv_bin.bin not like 'defbin'
and inv_bin.bin not like 'Defaulbin'
/*look up filtering out ids based on not like statements*/

ORDER BY inv_bin.inv_mast_uid;

Thanks again for all of your help.

Upvotes: 0

Views: 7624

Answers (1)

fspinnenhirn
fspinnenhirn

Reputation: 1889

this isn't an answer, just a question/comment:

Without knowing any more details about your database schema, I'm wondering if you're simply using the wrong table for your group-by clause?

If your "inv_mast" table only contained unique "item_id"s, then you'd never get a count higher than one from that particular table.

Try counting on the IDs on the "inv_bin"-table instead:

SELECT item_id 
FROM inv_bin
GROUP BY item_id HAVING COUNT(item_id) > 1

Upvotes: 1

Related Questions