Group By and Having are driving me crazy!

[xtech1005]

Hello,

I'm trying to get a Select query in a stored proc to give the correct results but all I've done so far is pull hair out. Maybe one of you gurus out there can help me!

I have a table with information like this (sorry, couldn't make this look right):

Field1....................... Field2
ABC.........................6
XYZ123.....................6
XYZ123.....................14
XYZ123.....................30
JKL456......................6
JKL456.....................14
JKL456.....................30
NOP987....................14
NOP987....................30
QRS001....................6
QRS001....................14
QRS001....................30

I want to get a distinct list of Field1 where Field2 = 6 but only if Field1 occurs more than once. The result I'm looking for would be:

XYZ123
JKL456
QRS001

This is making me crazy! Anybody got any ideas? Thanks in advance.

[ck9663]

Try:

select field1 from yourtable where field2 = 6 group by field1 having count(*) > 1
 

-- CK

[xtech1005]

I've tried that but it leaves out records. It only returns rows where Field1 AND Field2 occur more than once.

Here is the table with data.

ETF_AAA_Steve 14
ETF_AAA_Steve 30
ETF_AAA_Steve 6
ETF_Bethel_Fuel2 6
ETF_Citrus_Comp_Sales 6
ETF_Citrus_Comp_Sales 6
OPL_Stevie 14
OPL_Stevie 30
OPL_Stevie 6
PinonPltIn_Gen_Stat_DI1 30
PinonPltIn_Gen_Stat_DI1 6
Stevie_Battery_Voltage 14
Stevie_Battery_Voltage 30
Stevie_Battery_Voltage 6
Stevie_Board_Temp 14
Stevie_Board_Temp 30
Stevie_Board_Temp 6
Stevie_Charger_Voltage 14
Stevie_Charger_Voltage 30
Stevie_Charger_Voltage 6
Stevie_STN01 14
Stevie_STN01 30
Stevie_STN01 6

If I use this query -

SELECT field1 FROM #tb
WHERE field2 = 6
GROUP BY field1 HAVING COUNT(*) > 1

The only record I get is -

ETF_Citrus_Comp_Sales

[Delerna]

Haven't tested this but I think it will work.
You might need to adjust for syntax errors ???

 
Select Distinct b.Field1
FROM
(select Field1 from TheTable having count(Field1)>1) a
join TheTable b on a.Field1=b.Field1
where b.Field2=6
 

this bit
(select Field1 from TheTable having count(Field1)>1)
is a derived table that contains a list of Field1's that have more than 1 record

joining that to TheTable effectively filters out all records that don't have more than 1 record.
The where clause then filters out all records where Field2 is not equal to 6

Hope that helps!

[Delerna]

maybe a modification to CK's version would work also. Not sure without testing it?

select field1 from yourtable where field2 = 6 group by field1 having count(Field1) > 1
 

[xtech1005]

Haven't tested this but I think it will work.
You might need to adjust for syntax errors ???

 
Select Distinct b.Field1
FROM
(select Field1 from TheTable having count(Field1)>1) a
join TheTable b on a.Field1=b.Field1
where b.Field2=6
 

this bit
(select Field1 from TheTable having count(Field1)>1)
is a derived table that contains a list of Field1's that have more than 1 record

joining that to TheTable effectively filters out all records that don't have more than 1 record.
The where clause then filters out all records where Field2 is not equal to 6

Hope that helps!

Delerna, that's pretty cool. I had to modify your example but it works great!

 
Select Distinct b.Field1
FROM
(select Field1 from TheTable GROUP BY TheTable.Field1 having count(Field1)>1) a
join TheTable b on a.Field1=b.Field1
where b.Field2=6
 

Many thanks to you and CK for your assistance! Thank you.