CODEWITHSUNDEEP
androidmysqlsqlite
almisoft
almisoft

Reputation: 2183

Sum of Avg of Groups in SQLite

Simple table "test":

Name Value
A    2
A    3
B    2
B    3
B    5
C    7

With a simple query I can get the following result:

select Name, count(*), sum(Value), avg(Value) from test group by Name

    Count Sum   Avg
A     2    5    2,50
B     3   10    3,33
C     1    7    7,00

The total count, sum and avg are easy, too:

select count(*), sum(Value), avg(Value) from test

      6   22    3,67

But how to query the sum of avg?

12,83 = 2,5 + 3,33 + 7,00

Needed result:

    Count Sum   Avg   Avg / 12,83 * 100
A     2    5    2,50  19% = 2,50 / 12,83 * 100  
B     3   10    3,33  26%
C     1    7    7,00  55%

Upvotes: 0

Views: 718

Answers (2)

CL.
CL.

Reputation: 180162

Computing the aggregate of an aggregate requires two steps, i.e., a subquery. The entire 12.83 computation is independent from any other rows, so it must be put into another subquery:

SELECT COUNT(Value) AS Count,
       SUM(Value)   AS Sum,
       AVG(Value)   AS Avg,
       AVG(Value) / (SELECT SUM(Avg)
                     FROM (SELECT AVG(Value) AS Avg
                           FROM Test
                           GROUP BY Name)
                    ) * 100 AS AvgPercentage
FROM Test
GROUP BY Name

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

The easiest way in SQLite is to use a CTE:

with t as (
      select Name, count(*) as cnt, sum(Value) as sumv, avg(Value) as avgv
      from test
      group by Name
     )
select t.*, (avgv / tt.sumavgv) as ratio
from t cross join
     (select sum(avgv) as sum_avgv from t) tt;

Upvotes: 0

Related Questions