Avg of a Sum in one query

Question

I would like to know if I can get the average of a sum in one single SQL SERVER request,

Have tried to do it with the following request but it doesn't work:

  SELECT t.client, 
         AVG(SUM(t.asset)) AS Expr1
    FROM TABLE t
GROUP BY t.client

Solution 1

I think your question needs a bit of explanation. If you want to take the sums grouped by t.client you can use:

SELECT t.client, SUM(t.asset)
FROM the-table t
GROUP BY t.client

Then, if you want to take the average of this sume, just make:

SELECT AVG(asset_sums)
FROM
(
    SELECT t.client, SUM(t.asset) AS asset_sums
    FROM the-table t
    GROUP BY t.client
) as inner_query

You can't however group the outer query, because this will give you results like in the first query. The results from the inner query are already grouped by t.client.

Solution 2

Its very simple

for ex.

 SELECT t.client, 
         SUM(t.asset)/count(t.asset) AS average
    FROM TABLE t
GROUP BY t.client

in "average" you will get average of "t.asset"

Solution 3

If you are trying to get the average assets of clients I think I would use at CTE. You can try if sql 2005 or greater

EDIT: Took the second group by out as I think you want just one result here of average assets

With clientsum (client,assets)
as
(
    SELECT     CLIENT, SUM(asset) AS assets
    FROM         CLIENTTABLE
    GROUP BY CLIENT
)
SELECT avg(assets) AS Expr1
FROM  clientsum