Easy aggregation question

Given this table
CREATE TABLE SearchFieldDetail (ExpSetNo int, TableAlias char(10), Operator
char(2))
which has multiple rows for each ExpSetNo, I want to find each ExpSetNo that
has only one TableAlias among all of the same ExpSetNo rows and which has
'CC' as the Operator in all those rows.
I would only want to return ExpSetNo 1 from this data:
INSERT SearchFieldDetail
SELECT 1, 'COMCF', 'CC'
UNION
SELECT 1, 'COMCF', 'CC'
UNION
SELECT 2, 'COMCF', 'CC'
UNION
SELECT 2, 'COC', '='
UNION
SELECT 3, 'COC', 'CC'
UNION
SELECT 3, 'COMCF', 'CC'
And here's what I'm doing.
SELECT ExpSetNo
FROM SearchFieldDetail
GROUP BY ExpSetNo
HAVING COUNT(TableAlias) = 1 AND SUM(CASE Operator WHEN 'CC' THEN 0 ELSE 1
END) = 0
DROP TABLE SearchFieldDetail
This works, but are there any suggestions for improvement?> I want to find each ExpSetNo that
> has only one TableAlias among all of the same ExpSetNo rows
In this case, you need to add DISTINCT to your COUNT function like the
example below. Also, I believe your sample data insert query needs to
specify UNION ALL rather than UNION.
SELECT ExpSetNo
FROM SearchFieldDetail
GROUP BY ExpSetNo
HAVING COUNT(DISTINCT TableAlias) = 1 AND SUM(CASE Operator WHEN 'CC' THEN 0
ELSE 1
END) = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Wilden" <mark@.mwilden.com> wrote in message
news:xqadnVdqk_RebmvcRVn-sg@.sti.net...
> Given this table
> CREATE TABLE SearchFieldDetail (ExpSetNo int, TableAlias char(10),
> Operator
> char(2))
> which has multiple rows for each ExpSetNo, I want to find each ExpSetNo
> that
> has only one TableAlias among all of the same ExpSetNo rows and which has
> 'CC' as the Operator in all those rows.
> I would only want to return ExpSetNo 1 from this data:
> INSERT SearchFieldDetail
> SELECT 1, 'COMCF', 'CC'
> UNION
> SELECT 1, 'COMCF', 'CC'
> UNION
> SELECT 2, 'COMCF', 'CC'
> UNION
> SELECT 2, 'COC', '='
> UNION
> SELECT 3, 'COC', 'CC'
> UNION
> SELECT 3, 'COMCF', 'CC'
> And here's what I'm doing.
> SELECT ExpSetNo
> FROM SearchFieldDetail
> GROUP BY ExpSetNo
> HAVING COUNT(TableAlias) = 1 AND SUM(CASE Operator WHEN 'CC' THEN 0 ELSE 1
> END) = 0
> DROP TABLE SearchFieldDetail
> This works, but are there any suggestions for improvement?
>
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eCvRsJ1AFHA.2104@.TK2MSFTNGP14.phx.gbl...
> In this case, you need to add DISTINCT to your COUNT function like the
> example below. Also, I believe your sample data insert query needs to
> specify UNION ALL rather than UNION.
> SELECT ExpSetNo
> FROM SearchFieldDetail
> GROUP BY ExpSetNo
> HAVING COUNT(DISTINCT TableAlias) = 1 AND SUM(CASE Operator WHEN 'CC' THEN
0
> ELSE 1
> END) = 0
Excellent, Dan - thanks. A good example of bad test data (created without
UNION ALL) giving misleading results.