TSQL -- find records in table with multiples in one column, and at least one specific occurrence of a value in another column

Question

TSQL -- find records in table with multiples in one column, and at least one specific occurrence of a value in another column

If I have: ourDB.dbo.ourTable with col1 and col2 and col 3

I want to find occurrences such that * A value of col1 occurs multiple times * at least one instance of col2 = 'Val1' at least once.

TSQL -- find specific occurrence in table

So one would start with:

Select col1, col2, col3
FROM ourDB.dbo.ourTable
having count(col1) > 1
WHERE
(col2 = 'Val1')
Group by col1, col2, col3
Order by col1, col2, col3

This would find where col2 always occurs with 'Val1', but how is this generalized to Col2 having 'Val1' at least once ?

Still not sure why there is a down vote here? – JosephDoggie Jul 17 '20 at 12:06 — JosephDoggie, Jul 17 '20 at 12:06

forpas · Accepted Answer · 2019-08-21T21:17:53.670

You must GROUP BY col1 only and with conditional aggregation you get all the col1 values you need:

SELECT * FROM ourDB.dbo.ourTable
WHERE col1 IN (
  SELECT col1
  FROM ourDB.dbo.ourTable
  GROUP BY col1
  HAVING COUNT(*) > 1 AND SUM(CASE WHEN col2 = 'Val1' THEN 1 END) > 0
)
ORDER BY col1, col2, col3

If you want only the rows with col2 = 'Val1':

SELECT * FROM ourDB.dbo.ourTable
WHERE
  col2 = 'Val1'
  AND 
  col1 IN (
    SELECT col1
    FROM ourDB.dbo.ourTable
    GROUP BY col1
    HAVING COUNT(*) > 1 AND SUM(CASE WHEN col2 = 'Val1' THEN 1 END) > 0
  )
  ORDER BY col1, col3

JosephDoggie · Answer 2 · 2020-07-17T12:05:44.303

Alternate method below, originally from a colleague, I modified it some. NOTE: Not necessarily better than other (accepted) answer, just different approach.

 -- GENERIC_JOIN_WITH_SPECIFIC_COUNTS_Q_v_0.sql
    USE [ourDB]
    SELECT COUNT( distinct titleid)  -- also could use COUNT(*)  
    from ourTable
    
    WHERE 
    (
        (1 < 
          (
            select count( col1) 
            from ourTable
            GROUP BY col1
            HAVING (count(col1 > 1) 
            -- more than one col1 occurence
            )
         )
    
        AND 
        (0 < 
          (
            select count(*) from ourTable
            WHERE( col2 = 'Val1' ) 
            -- at least one occurence of col2 having 'Val1'
          )
         )
    )

TSQL -- find records in table with multiples in one column, and at least one specific occurrence of a value in another column

2 Answers2