sforsandeep sforsandeep - 3 months ago 8
SQL Question

SQL Group by with where condition

I've a table like this

TestID SectionID Section
-------------------------------
1 5 1
1 6 2
2 7 1
2 8 2
3 9 1
4 10 1
4 11 2
5 12 2
6 13 1
6 14 2
7 15 2
8 16 1
8 17 2
---------------------------------


The scenario is to return testID with sectionID 12 and 15. So the condition is return those rows where non repeating testID and the section should be 2. Thus sectionID 12 and 15 is returning because, for there testIDs (5 and 7 respectively) there is only row for that testid 5 and 7 and there section is 2. Row 3 wont be selected because even its testid not repeating, but its section is 1

I tried using group by

select testid, count(section)
from #temp
where section = 2
group by testid, section
having count(section) = 1


But its returning wrong testids.

Here is the code snippet with the data I've tried.

create table #temp (testid int,sectionid int,section int)
insert into #temp (testid,sectionid,section)
values (1,5,1),(1,6,2),(2,7,1),(2,8,2),(3,9,1),(4,10,1),(4,11,2),(5,12,2),(6,13,1),(6,14,2),(7,15,2),(8,16,1),(8,17,2)
select testid,count(testid) from #temp where section=1
group by testid having count(testid)=1


So the expected result is

TestID
---------
5
7
---------

Answer

I think this is the condition:

select testid
from #test t
group by testid
having count(*) = 1 and
       min(section) = 2;

The first condition checks that there is only one row. The second condition checks that the section value in the row is "2". Note that with just one row, you could use either min() or max().