Search code examples
sqlsql-servert-sqlsumaverage

Avg of a Sum in one query

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

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