CODEWITHSUNDEEP
sqlsql-server
James
James

Reputation: 43

Adding averages to SQL query

I am trying to create a query that shows me the average sales between different countries but I also want to add a 4th column with which shows the total average for that country...

This is my query to select the averages between countries which works ok:

SELECT Avg([Sales]), [From Country],[To Country], 
FROM [DB]
GROUP by [From Country],[To Country]

But I also want to add a 4th column that gives the total average sales for [From Country], can this be done?

Upvotes: 0

Views: 72

Answers (2)

LukStorms
LukStorms

Reputation: 29647

You could also make use of a ROLLUP

SELECT [From Country], [To Country], [Avg], [Avg From]
FROM
(
    SELECT [From Country], [To Country]
    , AVG([Sales]) AS [Avg]
    , MAX(CASE WHEN GROUPING_ID([From Country], [To Country])  = 1 THEN AVG([Sales]) END) OVER (PARTITION BY [From Country]) AS [Avg From]
    , GROUPING_ID([From Country], [To Country]) AS GroupingId
    FROM [DB]
    GROUP BY [From Country], [To Country]
    WITH ROLLUP
) q
WHERE GroupingId = 0;

Test here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You have to be careful here. You can use window functions but to get an unbiased average, you need to calculate the total by the total count:

SELECT [From Country], [To Country], Avg([Sales]), 
       SUM(SUM(Sales)) OVER (PARTITION BY [From Country]) / SUM(COUNT(*)) OVER (PARTITION BY [From Country])
FROM [DB]
GROUP by [From Country], [To Country];

Note that the results are different from:

AVG(AVG(SALES)) OVER (PARTITION BY [From Country])

This is a biased (or weighted) average that treats each TO COUNTRY equally.

Upvotes: 1

Related Questions