SQL Server query for multiple conditions on the same column

Here's the schema and data that i am working with

CREATE TABLE tbl (
    name varchar(20) not null,
    groups int NOT NULL
);

insert into tbl values('a', 35);
insert into tbl values('a', 36);
insert into tbl values('b', 35);
insert into tbl values('c', 36);
insert into tbl values('d', 37);

| name | groups|
|------|-------|
| a    | 35    |
| a    | 36    |
| b    | 35    |
| c    | 36    |
| d    | 37    |

now i need names of only those that are having group greater than or equal to 35 but also an additional is that i can only include a row for which group=35 when a corresponding groups=36 is also present

| name | groups|
|------|-------|
| a    | 35    |
| a    | 36    |

second condition is that it CAN include those names that are having groups greater than or equal to 36 without having a groups=35

| name | groups|
|------|-------|
| c    | 36    |
| d    | 37    |

the only case it should leave out is where a record has only groups=35 present without a corresponding groups=36

| name | groups|
|------|-------|
| b    | 35    |

i have tried the following

select name from tbl
where groups>=35
group by name
having count(distinct(groups))>=2
or groups>=36;

this is the error i am facing Column 'tbl.groups' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Solution

Try this:

DECLARE @tbl table ( [name] varchar(20) not null, groups int NOT NULL );

INSERT INTO @tbl VALUES
    ('a', 35), ('a', 36), ('b', 35), ('c', 36), ('d', 37);

DECLARE @group int = 35;

; WITH cte AS (
    SELECT
        [name]
        , COUNT ( DISTINCT groups ) AS distinct_group_count
    FROM @tbl
    WHERE
        groups >= @group
    GROUP BY
        [name]
)
SELECT t.* FROM @tbl AS t
INNER JOIN cte
    ON t.[name] = cte.[name]
WHERE
    cte.distinct_group_count > 1
    OR t.groups > @group;

RETURNS

+------+--------+
| name | groups |
+------+--------+
| a    |     35 |
| a    |     36 |
| c    |     36 |
| d    |     37 |
+------+--------+

Basically, this restricts the name results to groups with a value >= 35 with more than one distinct group associated, or any name with a group value greater than 35. Several assumptions were made in regard to your data, but I believe the logic still applies.