CODEWITHSUNDEEP
sqlsql-servert-sqlsumaverage
Roch
Roch

Reputation: 22061

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

Upvotes: 32

Views: 103414

Answers (4)

sreekuttan
sreekuttan

Reputation: 1

sum(avg(value) from * tablname

Upvotes: 0

Lukasz Lysik
Lukasz Lysik

Reputation: 10630

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.

Upvotes: 53

Kalpesh Gohel
Kalpesh Gohel

Reputation: 374

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"

Upvotes: 21

Gratzy
Gratzy

Reputation: 9389

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

Upvotes: 4

Related Questions