SELECT DISTINCT results that only returns COUNT > 1

user_14676447 0
Viewed 64

I have a database representing multiple items per box. Each box is represented by a barcode, and each box also has a non-unique tag field. So a sample of the table might look like this:

| Item | Box_Barcode | Box_Tag |
| I001 | Box0001     | TagA    |
| I002 | Box0001     | TagA    |
| I003 | Box0002     | TagB    |
| I004 | Box0002     | TagB    |
| I005 | Box0003     | TagC    |
| I006 | Box0003     | TagD    |
| I007 | Box0004     | TagE    |
| I008 | Box0004     | TagF    |

What I'm trying to get is a query that only returns distinct combinations of Box_Barcode and Box_Tag where there is more than one Box_Tag per Box_Barcode. For the sample above, I would expect this:

| Box_Barcode | Box_Tag |
| Box0003     | TagC    |
| Box0003     | TagD    |
| Box0004     | TagE    |
| Box0004     | TagF    |

Box001 and Box002 are not returned because they each have only one Box_Tag associated with them.

I keep trying a variation of the following:

    SELECT DISTINCT box_barcode, box_tag
    FROM TableName
    GROUP BY box_barcode, box_tag
    HAVING COUNT(box_barcode) > 1
    ORDER BY box_barcode

The variations I've tried include using "box_tag" and the asterisk in the HAVING clause. However, the results that I get list Box_Barcodes with singular Box_Tags. So, Box0001 and Box0002 from the sample above. However, the number of rows returned is much less than if I took the HAVING line out of the code. Obviously, I'm missing something.

I've seen this question asked before, but it has always involved one field in the SELECT statement, and I can't figure out the syntax for multiple fields.

sql-server sql
3 Answers

A simpler method is EXISTS:

SELECT t.*. -- whatever columns you want
FROM TableName
WHERE EXISTS (SELECT 1
              FROM TableName t2
              WHERE t2.box_barcode = t.box_barcode and
                    t2.box_tag <> t.box_tag
             );

You can still use COUNT(DISTINCT) if you want.

For performance, you want an index on (box_barcode, box_tag).

user_11440350

The most straightforward way can be COUNT(DISTINCT ...) as analytical function. But since for SQL Server it is not implemented and you need just >1, then it is sufficient to have MIN(BOX_TAG) <> MAX(BOX_TAG).

with a as (
  select 'I001' as item, 'Box0001' as box_barcode, 'TagA' as box_tag union all
  select 'I002' as item, 'Box0001' as box_barcode, 'TagA' as box_tag union all
  select 'I003' as item, 'Box0002' as box_barcode, 'TagB' as box_tag union all
  select 'I004' as item, 'Box0002' as box_barcode, 'TagB' as box_tag union all
  select 'I005' as item, 'Box0003' as box_barcode, 'TagC' as box_tag union all
  select 'I006' as item, 'Box0003' as box_barcode, 'TagD' as box_tag union all
  select 'I007' as item, 'Box0004' as box_barcode, 'TagE' as box_tag union all
  select 'I008' as item, 'Box0004' as box_barcode, 'TagF' as box_tag
)
, count_dist as (
  select a.*
    , min(box_tag) over(partition by box_barcode) as mn
    , max(box_tag) over(partition by box_barcode) as mx
  from a
)
select distinct
  box_barcode,
  box_tag
from count_dist
where mn <> mx

box_barcode | box_tag
:---------- | :------
Box0003     | TagC   
Box0003     | TagD   
Box0004     | TagE   
Box0004     | TagF

db<>fiddle

user_27787100

Your query is almost fine, just drop DISTINCT and add star to COUNT

SELECT box_barcode, box_tag
    FROM TableName
    GROUP BY box_barcode, box_tag
    HAVING COUNT(*) > 1
    ORDER BY box_barcode

Edit based on comments:

As I understood the issue is that you didn't account for duplicated Box_Tag for single Box_Barcode. To resolve this you need to distinct before counting or using NOT EXISTS as @Gordon mentioned:

SELECT t.box_barcode, t.box_tag
FROM (
    SELECT distinct box_barcode, box_tag
    FROM TableName
) AS t
WHERE NOT EXISTS (
    SELECT 1
    FROM TableName t2
    WHERE t2.box_barcode = t.box_barcode 
        AND t2.box_tag <> t.box_tag -- Checking for any other combo
    );
user_86362780
Related