SQL query to fetch a column where count is greater than 1 in the same table

Question

I am facing an issue with where the table structure is as below

SHIPMENT_GID || COST_TYPE || COST
12233        ||B          ||1180
12234        ||B          ||1300
12235        ||B          ||1100
12236        ||B          ||1400
12233        ||B          ||200
12233        ||A          ||300

Here I want is the SQL query should fetch me unique shipment Id that has Count(cost_type = B) >1

I had the following query but it is not working

select * from shipment_cost where shipment_gid = (select SHIPMENT_GID 
                                                  from shipment_cost 
                                                  where (count(COST_TYPE = 'B')>1)

score 4 · Accepted Answer · edited Aug 30 '19 at 14:23

4

This should do it:

 SELECT SHIPMENT_GI, count(*)
 FROM shipment_cost
 WHERE COST_TYPE='B' 
 GROUP BY SHIPMENT_GI
 HAVING COUNT(*) > 1

edited Aug 30 '19 at 14:23

Ankit Bajpai

13,128
4
25
40

answered Aug 30 '19 at 14:23

Bruce

1,542
13
17

1

@mrugen `HAVING COUNT(SHIPMENT_GI) > 1` would be better when `SHIPMENT_GI` can be `NULL` as `COUNT(*)` also count `NULL` values where `COUNT()` does not , not sure that results you are after? – Raymond Nijland Aug 30 '19 at 14:46
Thanks, Bruce it worked – mrugen munshi Aug 30 '19 at 15:41
See @Raymond Nijland comment in cases of nulls. – Bruce Aug 30 '19 at 15:48

SQL query to fetch a column where count is greater than 1 in the same table

1 Answers1