Group By and Having are driving me crazy!

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.

re: Group By and Having are driving me crazy!

Try:


-- CK

re: Group By and Having are driving me crazy!

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

re: Group By and Having are driving me crazy!

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

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!

re: Group By and Having are driving me crazy!

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

re: Group By and Having are driving me crazy!

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

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