SQL SERVER 200 and GROUP BY and HAVING with COUNT 1

[davism]

Hi all,

I'm having a little bit of difficult and hopefully somebody can help me out here.

I have a table like:

GUID, TESTID, TESTDATE, TESTTYPE, TESTTYPEID

Now what I want to do is I know there are multiple rows with the same TESTID for the same TESTTYPEID.

So, I am trying to get an output of those for the same TESTID.

I am trying this:

SELECT TESTID, TESTDATE from MyTestTable WHERE TestTypeID=1100 and (TESTDATE >= '2014-09-03' and TESTDATE < '2014-09-04')
GROUP BY TESTID, TESTDATE
HAVING COUNT(TESTID) >=2

Now, this is not returning anything but I know for a fact there are records in the table that meet that situation. If I take out the HAVING clause then it will show them to and I see it but I also see all the others too. I only want the ones that have the COUNT(TESTID) >= 2

What am I doing incorrectly here?

This is SQL SERVER 2000.

Thanks

 

[TamarGranor]

Take TestDate out of the field list and the GROUP BY list.

Tamar

 

[RyanEK]

Hi,

This what you're after?...

declare @temp table (guid int, testid int, testtypeid int)
insert into @temp select 1, 1, 1
insert into @temp select 2, 1, 1
insert into @temp select 3, 1, 2
insert into @temp select 5, 1, 3
insert into @temp select 6, 1, 3
insert into @temp select 7, 1, 4

select * 
from @temp t1
join (
	select testid, testtypeid
	from @temp
	group by testid, testtypeid
	having count(0) > 1
) t2 on t1.testid = t2.testid and t1.testtypeid = t2.testtypeid

Ryan

 

[davism]

Hi RyanEK

No, unfortunately, that is not going to work.

If you take the example of:

insert into @temp select 2, 1, 1
insert into @temp select 3, 1, 2
insert into @temp select 5, 1, 3
insert into @temp select 6, 1, 3
insert into @temp select 7, 1, 4
insert into @temp select 8, 2, 3
insert into @temp select 9, 3, 5
insert into @temp select 10, 1, 2

I would want the information for like:

3, 1, 2
10, 1, 2

Because I want to list TestID's of 1 with a TestTypeID of 2.

Obviously there would be a TestDate with row as well but for the sake of brevity, the TestGUID would work in this case (i.e. the 3 and the 10, in the example.)

I hope that clears it up some.

But how can I do that?

 

[gk53]

but code below produce
3, 1, 2
10, 1, 2

declare @temp table (guid int, testid int, testtypeid int)
insert into @temp select 2, 1, 1
insert into @temp select 3, 1, 2
insert into @temp select 5, 1, 3
insert into @temp select 6, 1, 3
insert into @temp select 7, 1, 4
insert into @temp select 8, 2, 3
insert into @temp select 9, 3, 5
insert into @temp select 10, 1, 2 

select t1.* 
from @temp t1
join (
	select testid, testtypeid
	from @temp
	group by testid, testtypeid
	having count(0) > 1
) t2 on t1.testid = t2.testid and t1.testtypeid = t2.testtypeid

 

[davism]

Correct, but it also produced:

5, 1, 3
6, 1, 3

BUT that was an easy fix as with the "on" condition I added "...and t1.testtypeid=2".

The results sure enough only gave me the

3, 1, 2
10, 1, 2

Let me review this a little more and apply it.

 

[davism]

That is working out great!

Thanks so much and very much appreciate that.