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

Answer 1

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

Answer 2

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
) 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.