Getting an average

27 Jul 2006 9:15 PM

KatMagic

I have a query that returns a sum of payments for each year for one client.
What I need is an average of that sum, and I cannot figure out how to do
that. Can anyone help?

This is my query to get the sum for each year:
Select PaymentYear, sum(payment) from tblClientPayments
    where ClientID = @ClientID

A sample of results for client John Die:
1999    $101.00
1998    $ 20.00
1990    $325.00

I want to be able to get the average of those 3, the result would be:
148.66 (446/3). How can I do that?

Thank you for your help.

27 Jul 2006 9:24 PM

David Portas

KatMagic wrote:
Show quote

> I have a query that returns a sum of payments for each year for one client.
> What I need is an average of that sum, and I cannot figure out how to do
> that. Can anyone help?
>
> This is my query to get the sum for each year:
> Select PaymentYear, sum(payment) from tblClientPayments
>     where ClientID = @ClientID
>
> A sample of results for client John Die:
> 1999    $101.00
> 1998    $ 20.00
> 1990    $325.00
>
> I want to be able to get the average of those 3, the result would be:
> 148.66 (446/3). How can I do that?
>
> Thank you for your help.

SELECT AVG(payment) AS avg_payment
FROM
(SELECT SUM(payment) AS payment
   FROM tblClientPayments
   WHERE ClientID = @ClientID
   GROUP BY PaymentYear) AS t ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

27 Jul 2006 10:24 PM

Arnie Rowland

If I read correctly, it seems like you want both the sum and average for the same client for the entire year.

If that is correct, this may help.

SELECT
     PaymentYear
   , TotalPayments = sum( Payment )
   , AveragePayment = avg( Payment )
FROM tblClientPayments
WHERE ClientID = @ClientID

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"KatMagic" <SSKatMa***@yahoo.com> wrote in message news:%23%23UCOGcsGHA.1288@TK2MSFTNGP02.phx.gbl...
>I have a query that returns a sum of payments for each year for one client.
> What I need is an average of that sum, and I cannot figure out how to do
> that. Can anyone help?
>
> This is my query to get the sum for each year:
> Select PaymentYear, sum(payment) from tblClientPayments
>    where ClientID = @ClientID
>
> A sample of results for client John Die:
> 1999    $101.00
> 1998    $ 20.00
> 1990    $325.00
>
> I want to be able to get the average of those 3, the result would be:
> 148.66 (446/3). How can I do that?
>
> Thank you for your help.
>
>
>
>

27 Jul 2006 10:54 PM

KatMagic

That works great! Thank you so much everyone.

Show quote

"KatMagic" <SSKatMa***@yahoo.com> wrote in message
news:%23%23UCOGcsGHA.1288@TK2MSFTNGP02.phx.gbl...
>I have a query that returns a sum of payments for each year for one client.
>What I need is an average of that sum, and I cannot figure out how to do
>that. Can anyone help?
>
> This is my query to get the sum for each year:
> Select PaymentYear, sum(payment) from tblClientPayments
>    where ClientID = @ClientID
>
> A sample of results for client John Die:
> 1999    $101.00
> 1998    $ 20.00
> 1990    $325.00
>
> I want to be able to get the average of those 3, the result would be:
> 148.66 (446/3). How can I do that?
>
> Thank you for your help.
>
>
>
>