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_Fuel 2 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_Te mp 14
Stevie_Board_Te mp 30
Stevie_Board_Te mp 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 ???

[code=sql]

Select Distinct b.Field1
FROM
(select Field1 from TheTable having count(Field1)>1 ) a
join TheTable b on a.Field1=b.Fiel d1
where b.Field2=6
[/code]

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?

[code=sql]
select field1 from yourtable where field2 = 6 group by field1 having count(Field1) > 1
[/code]

[xtech1005]

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

[code=sql]

Select Distinct b.Field1
FROM
(select Field1 from TheTable having count(Field1)>1 ) a
join TheTable b on a.Field1=b.Fiel d1
where b.Field2=6
[/code]

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!

[code=sql]

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.Fiel d1
where b.Field2=6
[/code]

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