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.

Easiest way of combining multiple fields from different records into one record?

I have a table;

CREATE TABLE theLiterals (
theKey varchar (255) NOT NULL ,
theValue varchar (255) NULL
)
INSERT INTO theLiterals VALUES('defaultServer','\\MyServer\')
INSERT INTO theLiterals VALUES('defaultShare','MyShare\')
INSERT INTO theLiterals VALUES('defaultFolder','MyFolder\')
INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')

I then try;

SELECT
defaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,
defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,
defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,
defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue END
FROM theLiterals

and I get;

defaultServer defaultShare defaultFolder defaultFile
\\MyServer\ NULL NULL NULL
NULL MyShare\ NULL NULL
NULL NULL MyFolder\ NULL
NULL NULL NULL MyFile.dat

but I want it COALESCEd like this;

defaultServer defaultShare defaultFolder defaultFile
\\MyServer\ MyShare\ MyFolder\ MyFile.dat

...but my syntax is incorrect. Is there an efficient way of doing this.

I want to have a script/UDF where I can say...
GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile')
and then my one-row recordset will be...

RS(0) will = '\\MyServer\'
RS(1) will = 'MyShare\'
RS(2) will = 'MyFolder\'
RS(3) will = 'MyFile.dat'

Thanks for any help!Just add MIN():

SELECT
defaultServer = MIN(CASE WHEN theKey = 'defaultServer' THEN theValue END),
defaultShare = MIN(CASE WHEN theKey = 'defaultShare' THEN theValue END),
defaultFolder = MIN(CASE WHEN theKey = 'defaultFolder' THEN theValue END),
defaultFile = MIN(CASE WHEN theKey = 'defaultFile' THEN theValue END)
FROM theLiterals

--
David Portas
SQL Server MVP
--|||Hi

Maybe:
SELECT A.defaultServer, B.defaultShare, C.defaultFolder, D.defaultFile
FROM
( SELECT theValue AS defaultServer
FROM theLiterals
WHERE theKey = 'defaultServer' ) A,
( SELECT theValue AS defaultShare
FROM theLiterals
WHERE theKey = 'defaultShare' ) B,
( SELECT theValue AS defaultFolder
FROM theLiterals
WHERE theKey = 'defaultFolder' ) C,
( SELECT theValue AS defaultFile
FROM theLiterals
WHERE theKey = 'defaultFile' ) D

OR

SELECT
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultServer' ) AS defaultServer ,
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultShare' ) AS defaultShare ,
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultFolder' ) AS defaultFolder ,
( SELECT theValue
FROM theLiterals
WHERE theKey = 'defaultFile' ) AS defaultFile

You should put a unique or primary key on theKey to make sure only one row
is returned.

John

"Steve" <steve.lin@.cognizantdesign.com> wrote in message
news:27b20cea.0407090955.690c2c8b@.posting.google.c om...
> I have a table;
> CREATE TABLE theLiterals (
> theKey varchar (255) NOT NULL ,
> theValue varchar (255) NULL
> )
> INSERT INTO theLiterals VALUES('defaultServer','\\MyServer\')
> INSERT INTO theLiterals VALUES('defaultShare','MyShare\')
> INSERT INTO theLiterals VALUES('defaultFolder','MyFolder\')
> INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')
>
> I then try;
> SELECT
> defaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,
> defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,
> defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,
> defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue END
> FROM theLiterals
> and I get;
> defaultServer defaultShare defaultFolder defaultFile
> \\MyServer\ NULL NULL NULL
> NULL MyShare\ NULL NULL
> NULL NULL MyFolder\ NULL
> NULL NULL NULL MyFile.dat
> but I want it COALESCEd like this;
> defaultServer defaultShare defaultFolder defaultFile
> \\MyServer\ MyShare\ MyFolder\ MyFile.dat
> ...but my syntax is incorrect. Is there an efficient way of doing this.
> I want to have a script/UDF where I can say...
GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile')
> and then my one-row recordset will be...
> RS(0) will = '\\MyServer\'
> RS(1) will = 'MyShare\'
> RS(2) will = 'MyFolder\'
> RS(3) will = 'MyFile.dat'
> Thanks for any help!|||How about this:

SELECT TOP 1
defaultServer = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultServer'),
defaultShare = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultShare'),
defaultFolder = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultFolder'),
defaultFile = (SELECT theValue FROM #theLiterals
WHERE theKey = 'defaultFile')
FROM #theLiterals

That returns the desired record:

\\MyServer\MyShare\MyFolder\MyFile.dat

Or you could create a function that takes 4 parameters like 'defaultServer'
and returns a one-record table populated with the results from those 4
SELECTs.

Jim Geissman|||Missed the beginning of this thread, but if #theLiterals is not trivially
small,
you get an (avg) 2:1 speedup by doing:

SELECT
defaultServer = max(case theKey when 'defaultServer' then theValue
end)
,defaultShare = max(case theKey when 'defaultShare' then theValue
end)
,defaultFolder = max(case theKey when 'defaultFolder' then theValue
end)
,defaultFile = max(case theKey when 'defaultFile' then theValue
end)
FROM #theLiterals

"Jim Geissman" <jim_geissman@.countrywide.com> wrote in message
news:b84bf9dc.0407091511.6338405b@.posting.google.c om...
> How about this:
> SELECT TOP 1
> defaultServer = (SELECT theValue FROM #theLiterals
> WHERE theKey = 'defaultServer'),
> defaultShare = (SELECT theValue FROM #theLiterals
> WHERE theKey = 'defaultShare'),
> defaultFolder = (SELECT theValue FROM #theLiterals
> WHERE theKey = 'defaultFolder'),
> defaultFile = (SELECT theValue FROM #theLiterals
> WHERE theKey = 'defaultFile')
> FROM #theLiterals
> That returns the desired record:
> \\MyServer\ MyShare\ MyFolder\ MyFile.dat
> Or you could create a function that takes 4 parameters like
'defaultServer'
> and returns a one-record table populated with the results from those 4
> SELECTs.
> Jim Geissman|||And first place for minimum reads goes to David Portas!

Thanks everyone for the help. I originally thought doing an aggregate
function
to get rid of NULLS would be inefficient, but by looking at the TRACE
it looks
like it has the most efficient execution plan.

FYI, I listed each of your solutions and the number of reads each
took and some additional questions.

NOTE: The 'theLiterals' table would never be big enough to cause more
than a seconds execution but it is always best to strive for
efficiency anyway. I hope you agree.

-- David Portas
-- 6 reads
-- Warning: Null value is eliminated by an aggregate or other SET
operation.
-- Why is MIN so much faster than MAX?
SELECT
defaultServer = MIN(CASE WHEN theKey = 'defaultServer' THEN theValue
END),
defaultShare = MIN(CASE WHEN theKey = 'defaultShare' THEN theValue
END),
defaultFolder = MIN(CASE WHEN theKey = 'defaultFolder' THEN theValue
END),
defaultFile = MIN(CASE WHEN theKey = 'defaultFile' THEN theValue
END)
FROM theLiterals

-- Mischa Sandberg
-- 18 reads
-- Warning: Null value is eliminated by an aggregate or other SET
operation.
-- Why is MIN so much faster than MAX or is it the way the CASE-WHEN
is
-- formatted?
SELECT
defaultServer = max(case theKey when 'defaultServer' then theValue
end)
,defaultShare = max(case theKey when 'defaultShare' then theValue
end)
,defaultFolder = max(case theKey when 'defaultFolder' then theValue
end)
,defaultFile = max(case theKey when 'defaultFile' then theValue
end)
FROM theLiterals

-- John Bell
-- 24 reads
SELECT
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultServer' ) AS
defaultServer ,
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultShare' ) AS
defaultShare ,
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultFolder' ) AS
defaultFolder ,
( SELECT theValue FROM theLiterals WHERE theKey = 'defaultFile' ) AS
defaultFile

-- John Bell
-- 24 reads
SELECT A.defaultServer, B.defaultShare, C.defaultFolder,
D.defaultFile
FROM
( SELECT theValue AS defaultServer FROM theLiterals WHERE theKey =
'defaultServer' ) A,
( SELECT theValue AS defaultShare FROM theLiterals WHERE theKey =
'defaultShare' ) B,
( SELECT theValue AS defaultFolder FROM theLiterals WHERE theKey =
'defaultFolder' ) C,
( SELECT theValue AS defaultFile FROM theLiterals WHERE theKey =
'defaultFile' ) D

-- Jim Geissman
-- 80 reads
-- Taking off the outside 'FROM theLiterals' returns only the one
record rather
-- than four duplicate records. Therefore the TOP function is then
not needed.
-- So the query becomes the same as John Bell's above with 24 reads
SELECT TOP 1
defaultServer = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultServer'),
defaultShare = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultShare'),
defaultFolder = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultFolder'),
defaultFile = (SELECT theValue FROM theLiterals WHERE theKey =
'defaultFile')
FROM theLiterals