Re-Post Sum of Sum from 2 tables

Thanks, it worked.
Is there a way to add another column to the query that shows the total of 1
column:
pieces volume Pieces Free Volume Free Invoiced TotInvoiced
1 1 0 0 10
50
1 2 0 0 20
50
1 1 0 0 20
50
Thanks in advance
Eric
"Mike Gemmell" <MikeGemmell@.discussions.microsoft.com> wrote in message
news:5D4193EA-C33B-4135-918B-9F78E13907A0@.microsoft.com...
> try
> select sum(QTUM1) as pieces, sum(QTUM2) as volume, sum(QTUM1O) as [Pieces
> free], sum(QTUM2O) as [Volume Free], sum(FATTM) as Invoiced from
> (
> select substring(productcode,1,1) code ,QTUM1, QTUM2, QTUM10, QTUM20,
> FATTM from stat001f where (myYear = 2004 and myMonth = 9)
> union all
> select substring(productcode,1,1) code, QTUM1, QTUM2, QTUM10, QTUM20,
FATTM
> from swstat0f where (myYear = 2004 and myMonth = 9)
> ) x
> group by code
>
> "ericvdb" wrote:
>
tables:
[Pieces
[PiecesTo get what you want you could do the following (there is probably a better
way of doing this though):
select sum(QTUM1) as pieces, sum(QTUM2) as volume, sum(QTUM1O) as [Pieces
free], sum(QTUM2O) as [Volume Free], sum(FATTM) as Invoiced, (select
sum(FATTM) from stat001f where (myYear = 2004 and myMonth = 9) + (select
sum(FATTM) from swstat0f where (myYear = 2004 and myMonth = 9) TotInvoiced
from
(
select substring(productcode,1,1) code ,QTUM1, QTUM2, QTUM10, QTUM20,
FATTM from stat001f where (myYear = 2004 and myMonth = 9)
union all
select substring(productcode,1,1) code, QTUM1, QTUM2, QTUM10, QTUM20, FATTM
from swstat0f where (myYear = 2004 and myMonth = 9)
) x
group by code
"ericvdb" wrote:

> Thanks, it worked.
> Is there a way to add another column to the query that shows the total of
1
> column:
> pieces volume Pieces Free Volume Free Invoiced TotInvoiced
> 1 1 0 0 10
> 50
> 1 2 0 0 20
> 50
> 1 1 0 0 20
> 50
>
> Thanks in advance
> Eric
> "Mike Gemmell" <MikeGemmell@.discussions.microsoft.com> wrote in message
> news:5D4193EA-C33B-4135-918B-9F78E13907A0@.microsoft.com...
> FATTM
> tables:
> [Pieces
> [Pieces
>
>

Re-Post Execution Plan

Hi,
Say if you had a store procedure called p_SelectData
defined as follows with the parameter @.WhereClause can by
set to either
"@.Where = ' Where Surname = ''Jones''...
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''.
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''
and HasDetails = 1
You get the idea, a dynamic where clause.
The SP is as follows
CREATE procedure dbo.p_SelectData
@.WhereClause varchar(100) as
DECLARE @.SQLString varchar(255)
SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
EXECUTE ( @.SQLString)
My question is can a proper execution plan be formed
internally by SQL Server, or does the exec with the where
clause stop it from forming ?
N.B. No I didn't implement this it was there when I got
there.
Thanks.Hi,
Firstly, you will always get an execution plan, but I'm guessing you are ask
ing if it will cache it and re-use it.
I would say that in your simple example, you will get an execution plan cach
ed for the stored procedure. In complex stored procedures, it is possible th
at the execution plan will be re-compiled during execution, but you can read
more about that in BOL.
As for the EXEC statment, I don't believe that the plan will be cached. The
reason being that the table name is not qualified with the owner/schema. It
is possible to have two tables with the same name but different owners, and
data and/or indexing is dif
ferent enough to result in a different execution plan. If the table name was
fully qualified, that I feel that it would use auto-parameterization.
"Jimbo" wrote:

> Hi,
> Say if you had a store procedure called p_SelectData
> defined as follows with the parameter @.WhereClause can by
> set to either
> "@.Where = ' Where Surname = ''Jones''...
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''.
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''
> and HasDetails = 1
> You get the idea, a dynamic where clause.
> The SP is as follows
> CREATE procedure dbo.p_SelectData
> @.WhereClause varchar(100) as
> DECLARE @.SQLString varchar(255)
> SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
> EXECUTE ( @.SQLString)
> My question is can a proper execution plan be formed
> internally by SQL Server, or does the exec with the where
> clause stop it from forming ?
> N.B. No I didn't implement this it was there when I got
> there.
> Thanks.
>
>|||Thanks Al, that was my thought on it as well, but I
thought I had better check my facts before continuing with
it.

>--Original Message--
>Hi,
>Firstly, you will always get an execution plan, but I'm
guessing you are asking if it will cache it and re-use it.
>I would say that in your simple example, you will get an
execution plan cached for the stored procedure. In complex
stored procedures, it is possible that the execution plan
will be re-compiled during execution, but you can read
more about that in BOL.
>As for the EXEC statment, I don't believe that the plan
will be cached. The reason being that the table name is
not qualified with the owner/schema. It is possible to
have two tables with the same name but different owners,
and data and/or indexing is different enough to result in
a different execution plan. If the table name was fully
qualified, that I feel that it would use auto-
parameterization.
>"Jimbo" wrote:
>
by[vbcol=seagreen]
Forename ''Paul''.[vbcol=seagreen]
Forename ''Paul''[vbcol=seagreen]
@.WhereClause[vbcol=seagreen]
where[vbcol=seagreen]
>.
>|||If you want to do a bit of experimenting, have a look at the Stored Procedur
e Events of the Profiler. These can show individual statements within the St
ored Procedure being executed and if there has been a Cache Hit or Cache Mis
s. That way it will be poss
ible to see if the EXEC call goes to the Cache or not
"Jimbo" wrote:

> Thanks Al, that was my thought on it as well, but I
> thought I had better check my facts before continuing with
> it.
>
>
> guessing you are asking if it will cache it and re-use it.
> execution plan cached for the stored procedure. In complex
> stored procedures, it is possible that the execution plan
> will be re-compiled during execution, but you can read
> more about that in BOL.
> will be cached. The reason being that the table name is
> not qualified with the owner/schema. It is possible to
> have two tables with the same name but different owners,
> and data and/or indexing is different enough to result in
> a different execution plan. If the table name was fully
> qualified, that I feel that it would use auto-
> parameterization.
> by
> Forename ''Paul''.
> Forename ''Paul''
> @.WhereClause
> where
>

Re-Post Execution Plan

Hi,
Say if you had a store procedure called p_SelectData
defined as follows with the parameter @.WhereClause can by
set to either
"@.Where = ' Where Surname = ''Jones''...
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''.
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''
and HasDetails = 1
You get the idea, a dynamic where clause.
The SP is as follows
CREATE procedure dbo.p_SelectData
@.WhereClause varchar(100) as
DECLARE @.SQLString varchar(255)
SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
EXECUTE ( @.SQLString)
My question is can a proper execution plan be formed
internally by SQL Server, or does the exec with the where
clause stop it from forming ?
N.B. No I didn't implement this it was there when I got
there.
Thanks.
Hi,
Firstly, you will always get an execution plan, but I'm guessing you are asking if it will cache it and re-use it.
I would say that in your simple example, you will get an execution plan cached for the stored procedure. In complex stored procedures, it is possible that the execution plan will be re-compiled during execution, but you can read more about that in BOL.
As for the EXEC statment, I don't believe that the plan will be cached. The reason being that the table name is not qualified with the owner/schema. It is possible to have two tables with the same name but different owners, and data and/or indexing is dif
ferent enough to result in a different execution plan. If the table name was fully qualified, that I feel that it would use auto-parameterization.
"Jimbo" wrote:

> Hi,
> Say if you had a store procedure called p_SelectData
> defined as follows with the parameter @.WhereClause can by
> set to either
> "@.Where = ' Where Surname = ''Jones''...
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''.
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''
> and HasDetails = 1
> You get the idea, a dynamic where clause.
> The SP is as follows
> CREATE procedure dbo.p_SelectData
> @.WhereClause varchar(100) as
> DECLARE @.SQLString varchar(255)
> SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
> EXECUTE ( @.SQLString)
> My question is can a proper execution plan be formed
> internally by SQL Server, or does the exec with the where
> clause stop it from forming ?
> N.B. No I didn't implement this it was there when I got
> there.
> Thanks.
>
>
|||Thanks Al, that was my thought on it as well, but I
thought I had better check my facts before continuing with
it.

>--Original Message--
>Hi,
>Firstly, you will always get an execution plan, but I'm
guessing you are asking if it will cache it and re-use it.
>I would say that in your simple example, you will get an
execution plan cached for the stored procedure. In complex
stored procedures, it is possible that the execution plan
will be re-compiled during execution, but you can read
more about that in BOL.
>As for the EXEC statment, I don't believe that the plan
will be cached. The reason being that the table name is
not qualified with the owner/schema. It is possible to
have two tables with the same name but different owners,
and data and/or indexing is different enough to result in
a different execution plan. If the table name was fully
qualified, that I feel that it would use auto-
parameterization.[vbcol=seagreen]
>"Jimbo" wrote:
by[vbcol=seagreen]
Forename ''Paul''.[vbcol=seagreen]
Forename ''Paul''[vbcol=seagreen]
@.WhereClause[vbcol=seagreen]
where
>.
>
|||If you want to do a bit of experimenting, have a look at the Stored Procedure Events of the Profiler. These can show individual statements within the Stored Procedure being executed and if there has been a Cache Hit or Cache Miss. That way it will be poss
ible to see if the EXEC call goes to the Cache or not
"Jimbo" wrote:

> Thanks Al, that was my thought on it as well, but I
> thought I had better check my facts before continuing with
> it.
>
> guessing you are asking if it will cache it and re-use it.
> execution plan cached for the stored procedure. In complex
> stored procedures, it is possible that the execution plan
> will be re-compiled during execution, but you can read
> more about that in BOL.
> will be cached. The reason being that the table name is
> not qualified with the owner/schema. It is possible to
> have two tables with the same name but different owners,
> and data and/or indexing is different enough to result in
> a different execution plan. If the table name was fully
> qualified, that I feel that it would use auto-
> parameterization.
> by
> Forename ''Paul''.
> Forename ''Paul''
> @.WhereClause
> where
>

Re-Post Execution Plan

Hi,
Say if you had a store procedure called p_SelectData
defined as follows with the parameter @.WhereClause can by
set to either
"@.Where = ' Where Surname = ''Jones''...
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''.
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''
and HasDetails = 1
You get the idea, a dynamic where clause.
The SP is as follows
CREATE procedure dbo.p_SelectData
@.WhereClause varchar(100) as
DECLARE @.SQLString varchar(255)
SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
EXECUTE ( @.SQLString)
My question is can a proper execution plan be formed
internally by SQL Server, or does the exec with the where
clause stop it from forming ?
N.B. No I didn't implement this it was there when I got
there.
Thanks.Hi,
Firstly, you will always get an execution plan, but I'm guessing you are asking if it will cache it and re-use it.
I would say that in your simple example, you will get an execution plan cached for the stored procedure. In complex stored procedures, it is possible that the execution plan will be re-compiled during execution, but you can read more about that in BOL.
As for the EXEC statment, I don't believe that the plan will be cached. The reason being that the table name is not qualified with the owner/schema. It is possible to have two tables with the same name but different owners, and data and/or indexing is different enough to result in a different execution plan. If the table name was fully qualified, that I feel that it would use auto-parameterization.
"Jimbo" wrote:
> Hi,
> Say if you had a store procedure called p_SelectData
> defined as follows with the parameter @.WhereClause can by
> set to either
> "@.Where = ' Where Surname = ''Jones''...
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''.
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''
> and HasDetails = 1
> You get the idea, a dynamic where clause.
> The SP is as follows
> CREATE procedure dbo.p_SelectData
> @.WhereClause varchar(100) as
> DECLARE @.SQLString varchar(255)
> SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
> EXECUTE ( @.SQLString)
> My question is can a proper execution plan be formed
> internally by SQL Server, or does the exec with the where
> clause stop it from forming ?
> N.B. No I didn't implement this it was there when I got
> there.
> Thanks.
>
>|||Thanks Al, that was my thought on it as well, but I
thought I had better check my facts before continuing with
it.
>--Original Message--
>Hi,
>Firstly, you will always get an execution plan, but I'm
guessing you are asking if it will cache it and re-use it.
>I would say that in your simple example, you will get an
execution plan cached for the stored procedure. In complex
stored procedures, it is possible that the execution plan
will be re-compiled during execution, but you can read
more about that in BOL.
>As for the EXEC statment, I don't believe that the plan
will be cached. The reason being that the table name is
not qualified with the owner/schema. It is possible to
have two tables with the same name but different owners,
and data and/or indexing is different enough to result in
a different execution plan. If the table name was fully
qualified, that I feel that it would use auto-
parameterization.
>"Jimbo" wrote:
>> Hi,
>> Say if you had a store procedure called p_SelectData
>> defined as follows with the parameter @.WhereClause can
by
>> set to either
>> "@.Where = ' Where Surname = ''Jones''...
>> or
>> @.Where = Where Surname = ''Jones'' and
Forename ''Paul''.
>> or
>> @.Where = Where Surname = ''Jones'' and
Forename ''Paul''
>> and HasDetails = 1
>> You get the idea, a dynamic where clause.
>> The SP is as follows
>> CREATE procedure dbo.p_SelectData
>> @.WhereClause varchar(100) as
>> DECLARE @.SQLString varchar(255)
>> SET @.SQLString = 'Select * From MockTable ' +
@.WhereClause
>> EXECUTE ( @.SQLString)
>> My question is can a proper execution plan be formed
>> internally by SQL Server, or does the exec with the
where
>> clause stop it from forming ?
>> N.B. No I didn't implement this it was there when I got
>> there.
>> Thanks.
>>
>.
>|||If you want to do a bit of experimenting, have a look at the Stored Procedure Events of the Profiler. These can show individual statements within the Stored Procedure being executed and if there has been a Cache Hit or Cache Miss. That way it will be possible to see if the EXEC call goes to the Cache or not
"Jimbo" wrote:
> Thanks Al, that was my thought on it as well, but I
> thought I had better check my facts before continuing with
> it.
>
> >--Original Message--
> >Hi,
> >
> >Firstly, you will always get an execution plan, but I'm
> guessing you are asking if it will cache it and re-use it.
> >
> >I would say that in your simple example, you will get an
> execution plan cached for the stored procedure. In complex
> stored procedures, it is possible that the execution plan
> will be re-compiled during execution, but you can read
> more about that in BOL.
> >
> >As for the EXEC statment, I don't believe that the plan
> will be cached. The reason being that the table name is
> not qualified with the owner/schema. It is possible to
> have two tables with the same name but different owners,
> and data and/or indexing is different enough to result in
> a different execution plan. If the table name was fully
> qualified, that I feel that it would use auto-
> parameterization.
> >
> >"Jimbo" wrote:
> >
> >> Hi,
> >>
> >> Say if you had a store procedure called p_SelectData
> >> defined as follows with the parameter @.WhereClause can
> by
> >> set to either
> >>
> >> "@.Where = ' Where Surname = ''Jones''...
> >> or
> >> @.Where = Where Surname = ''Jones'' and
> Forename ''Paul''.
> >> or
> >> @.Where = Where Surname = ''Jones'' and
> Forename ''Paul''
> >> and HasDetails = 1
> >>
> >> You get the idea, a dynamic where clause.
> >>
> >> The SP is as follows
> >>
> >> CREATE procedure dbo.p_SelectData
> >> @.WhereClause varchar(100) as
> >> DECLARE @.SQLString varchar(255)
> >> SET @.SQLString = 'Select * From MockTable ' +
> @.WhereClause
> >> EXECUTE ( @.SQLString)
> >>
> >> My question is can a proper execution plan be formed
> >> internally by SQL Server, or does the exec with the
> where
> >> clause stop it from forming ?
> >>
> >> N.B. No I didn't implement this it was there when I got
> >> there.
> >>
> >> Thanks.
> >>
> >>
> >>
> >.
> >
>

Re-Post - Process Information

Dear All,
Under Managment/Current Activity/Process Info you can see
all the users with connections to the system.
Here is the problem. A couple of users seem to have
multiple (10 or more) process ID's, all of them sleeping.
After a chat with them they said they were not in the
application, but the process was still being displayed.
My questions are then, should I be concerned, and why
aren't they closing automatically ?
Thanks
Peter> Dear All,
> Under Managment/Current Activity/Process Info you can see
> all the users with connections to the system.
> Here is the problem. A couple of users seem to have
> multiple (10 or more) process ID's, all of them sleeping.
> After a chat with them they said they were not in the
> application, but the process was still being displayed.
> My questions are then, should I be concerned, and why
> aren't they closing automatically ?
> Thanks
> Peter
--
Hi Peter,
It is possible that they are orphaned processes. Checkout this article:
INF: How to Troubleshoot Orphaned Connections in SQL Server
http://support.microsoft.com/?id=137983
Hope this helps,
Eric Crdenas
SQL Server senior support professional

Re-Post - Process Information

Dear All,
Under Managment/Current Activity/Process Info you can see
all the users with connections to the system.
Here is the problem. A couple of users seem to have
multiple (10 or more) process ID's, all of them sleeping.
After a chat with them they said they were not in the
application, but the process was still being displayed.
My questions are then, should I be concerned, and why
aren't they closing automatically ?
Thanks
Peter
> Dear All,
> Under Managment/Current Activity/Process Info you can see
> all the users with connections to the system.
> Here is the problem. A couple of users seem to have
> multiple (10 or more) process ID's, all of them sleeping.
> After a chat with them they said they were not in the
> application, but the process was still being displayed.
> My questions are then, should I be concerned, and why
> aren't they closing automatically ?
> Thanks
> Peter
Hi Peter,
It is possible that they are orphaned processes. Checkout this article:
INF: How to Troubleshoot Orphaned Connections in SQL Server
http://support.microsoft.com/?id=137983
Hope this helps,
Eric Crdenas
SQL Server senior support professional

Re-Post - Process Information

Dear All,
Under Managment/Current Activity/Process Info you can see
all the users with connections to the system.
Here is the problem. A couple of users seem to have
multiple (10 or more) process ID's, all of them sleeping.
After a chat with them they said they were not in the
application, but the process was still being displayed.
My questions are then, should I be concerned, and why
aren't they closing automatically ?
Thanks
Peter> Dear All,
> Under Managment/Current Activity/Process Info you can see
> all the users with connections to the system.
> Here is the problem. A couple of users seem to have
> multiple (10 or more) process ID's, all of them sleeping.
> After a chat with them they said they were not in the
> application, but the process was still being displayed.
> My questions are then, should I be concerned, and why
> aren't they closing automatically ?
> Thanks
> Peter
--
Hi Peter,
It is possible that they are orphaned processes. Checkout this article:
INF: How to Troubleshoot Orphaned Connections in SQL Server
http://support.microsoft.com/?id=137983
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional

Re-Post

Hi all,
I asked a question earlier about speeding up a union all statment like below
SELECT
code,
description,
clientDescription,
band,
AVG(rental) AS value,
COUNT(tableTemp.surveyID) AS [count],
CASE clientID
WHEN @.clientID THEN 1
ELSE 0
END AS color
FROM (SELECT tblClientSurvey.surveyID,
tblClientSurvey.clientID AS clientID,
client_alias AS code,
CONVERT(varchar(25), client_alias) AS description,
client_desc AS clientDescription,
#StartOfPeriod.band,
rental
FROM tblClientSurvey
INNER JOIN tblClient
ON tblClient.clientID = tblClientSurvey.clientID
INNER JOIN #surveys
ON #surveys.surveyID = tblClientSurvey.surveyID
INNER JOIN #StartOfPeriod
ON #StartOfPeriod.editionID=tblClientSurvey.editionID
WHERE (mileage = @.mileageint OR @.mileageint = 0)
AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
AND tblClientSurvey.editionID >= @.startEdition
AND tblClientSurvey.editionID < @.limitEdition
AND client_alias IS NOT NULL
AND rental IS NOT NULL
) AS tableTemp
GROUP BY code,
description,
clientDescription,
band,
tableTemp.clientID
UNION ALL
SELECT
'99' AS code,
'IMV' AS description,
'IMV' AS clientDescription,
band,
AVG(rental) AS value,
COUNT(averageTemp.surveyID) AS [count],
0 AS color
FROM (SELECT tblClientSurvey.surveyID,
#StartOfPeriod.band,
rental
FROM tblClientSurvey
INNER JOIN tblClient
ON tblClient.clientID = tblClientSurvey.clientID
INNER JOIN #surveys
ON #surveys.surveyID = tblClientSurvey.surveyID
INNER JOIN #StartOfPeriod
ON #StartOfPeriod.editionID=tblClientSurvey.editionID
WHERE (mileage = @.mileageint OR @.mileageint = 0)
AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
AND tblClientSurvey.editionID >= @.startEdition
AND tblClientSurvey.editionID < @.limitEdition
AND client_alias IS NOT NULL
AND rental IS NOT NULL
) AS averageTemp
GROUP BY band
I got a couple of reply but didn't get any response on my further related
questions regarding those reply. I have checked that all the index's are
correct, and I have to use the couple of temp tables referenced, just really
need a better way to write the select if anyone can help, osrry about having
to re-post.
Thanks in advance, PhilCan you tell us someting about the wanted result. Maybe there is an easier
way to solve your problem. Have you looked at the rollup function?
Martijn!
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:031AD5DC-5AFB-4896-814D-0DECFDE9A507@.microsoft.com...
> Hi all,
> I asked a question earlier about speeding up a union all statment like
below
> SELECT
> code,
> description,
> clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(tableTemp.surveyID) AS [count],
> CASE clientID
> WHEN @.clientID THEN 1
> ELSE 0
> END AS color
> FROM (SELECT tblClientSurvey.surveyID,
> tblClientSurvey.clientID AS clientID,
> client_alias AS code,
> CONVERT(varchar(25), client_alias) AS description,
> client_desc AS clientDescription,
> #StartOfPeriod.band,
> rental
> FROM tblClientSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblClientSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblClientSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblClientSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblClientSurvey.editionID >= @.startEdition
> AND tblClientSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS tableTemp
> GROUP BY code,
> description,
> clientDescription,
> band,
> tableTemp.clientID
> UNION ALL
> SELECT
> '99' AS code,
> 'IMV' AS description,
> 'IMV' AS clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(averageTemp.surveyID) AS [count],
> 0 AS color
> FROM (SELECT tblClientSurvey.surveyID,
> #StartOfPeriod.band,
> rental
> FROM tblClientSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblClientSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblClientSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblClientSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblClientSurvey.editionID >= @.startEdition
> AND tblClientSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS averageTemp
> GROUP BY band
> I got a couple of reply but didn't get any response on my further related
> questions regarding those reply. I have checked that all the index's are
> correct, and I have to use the couple of temp tables referenced, just
really
> need a better way to write the select if anyone can help, osrry about
having
> to re-post.
> Thanks in advance, Phil|||Try this query on the Northwind database. Maybe this is wat you look for.
Select distinct(e.lastname), s.companyname, sum(freight)
from orders o
inner join employees e
on o.employeeid = e.employeeid
inner join shippers s
on s.shipperid = o.shipvia
group by e.lastname, s.companyname with rollup
order by e.lastname, s.companyname asc
Good luck!
"M. de Jong" <martijn@.dejong.net> wrote in message
news:11297p0r8fj3t5d@.corp.supernews.com...
> Can you tell us someting about the wanted result. Maybe there is an easier
> way to solve your problem. Have you looked at the rollup function?
> Martijn!
>
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:031AD5DC-5AFB-4896-814D-0DECFDE9A507@.microsoft.com...
> below
related
are
> really
> having
>|||Hi there, yes I did take a look at the rollup function but it wasn't quite
what I was looking for, the results should looks something like :-
426 426 Company A 200404 406.728489 245 0
523 523 Company B 200404 394.861241 282 0
It does some analysis on a companies products on a month by month basis,
what there average value is and how many they have.
Hope this helps, Phil
"M. de Jong" wrote:

> Can you tell us someting about the wanted result. Maybe there is an easier
> way to solve your problem. Have you looked at the rollup function?
> Martijn!
>
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:031AD5DC-5AFB-4896-814D-0DECFDE9A507@.microsoft.com...
> below
> really
> having
>
>