globetrotter globetrotter - 2 months ago 9
SQL Question

Return groupings where at least one row per group satisfies a condition SQL server

I have a table (let's call it

TableA
) with multiple columns,
Id
being the unique id one.

I'm only interested in three:
A
(int),
B
(int),
C
(varchar).

Initially I want to pick up the rows which share the same
A
and
B
and return at least 2 rows.

; WITH CTE AS (
SELECT tbl.A, tbl.B
FROM [dbo].[TableA] tbl
/* WHERE irrelevant filter here */
GROUP BY tbl.A, tbl.B
HAVING COUNT(1) > 1
)


From this point on, I want to return the groupings identified in this CTE where AT LEAST one row in each grouping has it's
C
column set to
'ThisValue'
.

Answer Source

Use sum with case to count the records that have 'ThisValue'

; WITH CTE AS (
    SELECT      tbl.A, tbl.B
    FROM        [dbo].[TableA] tbl
    /* WHERE irrelevant filter here */
    GROUP BY    tbl.A, tbl.B
    HAVING      COUNT(1) > 1 and sum(case tbl.C when 'ThisValue' then 1 else 0 end)>0
)