Aggregate of an Aggregate

Sorry to be asking a stupid question by my search results have been futile this far.

Right now I have a list of Surveys and a list of Responses to each survey. What I need to do is get the average response of scores by survey. Which is to say I need to know the score for each survey and then average those results to make sure that no matter how many responses one survey receives, it carries no more weight than a survey with one response. Apologize if I'm asking an already answered question.

Pertinent Structure

tblResponse
objid
returnkey
response

tblSurvey
objid

Current Query:

SELECT
SUM(RE.Response) / COUNT(RE.Response) AS SurvScore
FROM
tblSurveyData SD INNER JOIN
tblResponses RE ON RE.Return_Key = SD.objid
GROUP BY
SD.objid

Ideally:

SELECT
AVG(SUM(RE.Response) / COUNT(RE.Response) AS AvScore FROM
tblSurveyData SD INNER JOIN
tblResponses RE ON RE.Return_Key = SD.objid
GROUP BY
SD.objidTry using an embedded subquery to calculate your statistics in two distinct steps:

SELECT AVG(SurvScore) AvScore
from
(SELECT SUM(RE.Response)/COUNT(RE.Response) AS SurvScore
FROM tblSurveyData SD
INNER JOIN tblResponses RE ON RE.Return_Key = SD.objid
GROUP BY SD.objid) SurveyScores

blindman|||Right on the money sir, I can't thank you enough

Agent jobs that copy files to network shares

I'm asking this question for a client of mine. They have SQL 2005 running on
a new server and have the AGENT running under a local server username - not a
network username.
They are trying to create a job under the agent and the only username that
appears in the drop-down for the step is this local server username. They
are trying to copy a file to a network share and this local un does not have
access to the share. How do you get more un's listed for the agent steps?
What is best practice in the field for what to have the agent run as?
I've always thought that having it run under a domain account would mean
that the domain server had to be up and running for SQL to be alive - not
like that's a problem - or is it?
What do you all do?
The SQLServerAgent runs under a domain account for us, that has writes to all
SQL Server Admin folders. I woulder perfer to minimize the control a bit,
but I think domain account is required. If PDC does go down, well I think
SQL will be least of the problems ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Steve Z" wrote:

> I'm asking this question for a client of mine. They have SQL 2005 running on
> a new server and have the AGENT running under a local server username - not a
> network username.
> They are trying to create a job under the agent and the only username that
> appears in the drop-down for the step is this local server username. They
> are trying to copy a file to a network share and this local un does not have
> access to the share. How do you get more un's listed for the agent steps?
> What is best practice in the field for what to have the agent run as?
> I've always thought that having it run under a domain account would mean
> that the domain server had to be up and running for SQL to be alive - not
> like that's a problem - or is it?
> What do you all do?

Agent jobs that copy files to network shares

I'm asking this question for a client of mine. They have SQL 2005 running on
a new server and have the AGENT running under a local server username - not a
network username.
They are trying to create a job under the agent and the only username that
appears in the drop-down for the step is this local server username. They
are trying to copy a file to a network share and this local un does not have
access to the share. How do you get more un's listed for the agent steps'
What is best practice in the field for what to have the agent run as?
I've always thought that having it run under a domain account would mean
that the domain server had to be up and running for SQL to be alive - not
like that's a problem - or is it?
What do you all do'The SQLServerAgent runs under a domain account for us, that has writes to all
SQL Server Admin folders. I woulder perfer to minimize the control a bit,
but I think domain account is required. If PDC does go down, well I think
SQL will be least of the problems ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Steve Z" wrote:
> I'm asking this question for a client of mine. They have SQL 2005 running on
> a new server and have the AGENT running under a local server username - not a
> network username.
> They are trying to create a job under the agent and the only username that
> appears in the drop-down for the step is this local server username. They
> are trying to copy a file to a network share and this local un does not have
> access to the share. How do you get more un's listed for the agent steps'
> What is best practice in the field for what to have the agent run as?
> I've always thought that having it run under a domain account would mean
> that the domain server had to be up and running for SQL to be alive - not
> like that's a problem - or is it?
> What do you all do'

Agent jobs that copy files to network shares

I'm asking this question for a client of mine. They have SQL 2005 running o
n
a new server and have the AGENT running under a local server username - not
a
network username.
They are trying to create a job under the agent and the only username that
appears in the drop-down for the step is this local server username. They
are trying to copy a file to a network share and this local un does not have
access to the share. How do you get more un's listed for the agent steps'
What is best practice in the field for what to have the agent run as?
I've always thought that having it run under a domain account would mean
that the domain server had to be up and running for SQL to be alive - not
like that's a problem - or is it?
What do you all do'The SQLServerAgent runs under a domain account for us, that has writes to al
l
SQL Server Admin folders. I woulder perfer to minimize the control a bit,
but I think domain account is required. If PDC does go down, well I think
SQL will be least of the problems ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Steve Z" wrote:

> I'm asking this question for a client of mine. They have SQL 2005 running
on
> a new server and have the AGENT running under a local server username - no
t a
> network username.
> They are trying to create a job under the agent and the only username that
> appears in the drop-down for the step is this local server username. They
> are trying to copy a file to a network share and this local un does not ha
ve
> access to the share. How do you get more un's listed for the agent steps?
?
> What is best practice in the field for what to have the agent run as?
> I've always thought that having it run under a domain account would mean
> that the domain server had to be up and running for SQL to be alive - not
> like that's a problem - or is it?
> What do you all do'

AGAIN Merge Replication with alternate synchronization partner

Hello I'm asking this without having answer:
I followed these instructions to set up replication:
http://support.microsoft.com/default...roduct=sql2k#3
From what I read in the help and everywhere this is soppoused to set the
PublisherB as the alternate synch parter. If I turn off the server
PublisherA and I need Publisher B and Subcriber A to replicate between
themselves while the Publisher A if off, what do I need to set up? What else
do I need to do? Because I turn off the server Publisher A and the
information is not being replicated between the other two servers. Again
we're having a huricane and we need to turn off the server and the other two
sites need to work normally as nothing happens.
Thanks and please help
Jennyfer
you need to set up server b with a copy of the database on server a. Then
you need to create the identical merge publication there as well.
Then you need to configure server b as an alternate publisher of server a.
You do this on server a using the sp_addmergealternatepublisher proc.
Then push your subscription to server c as a pull subscription.
When server a goes offline, you need to point server c to pull from server
b. You do this by right clicking on the merge pull agent, and change the
merge agent parameters to -Publisher ServerB -PublisherDB
PublisherDatabase -Distributor ServerB -SyncToAlternate 1
then restart your merge agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:OQjj0OrlEHA.3520@.tk2msftngp13.phx.gbl...
> Hello I'm asking this without having answer:
> I followed these instructions to set up replication:
>
http://support.microsoft.com/default...roduct=sql2k#3
> From what I read in the help and everywhere this is soppoused to set the
> PublisherB as the alternate synch parter. If I turn off the server
> PublisherA and I need Publisher B and Subcriber A to replicate between
> themselves while the Publisher A if off, what do I need to set up? What
else
> do I need to do? Because I turn off the server Publisher A and the
> information is not being replicated between the other two servers. Again
> we're having a huricane and we need to turn off the server and the other
two
> sites need to work normally as nothing happens.
> Thanks and please help
> Jennyfer
>