Home>

I want to get the sum and average of the values ​​for each specific condition in Access SQL.
Specifically, I would like to divide the conditions into'test1'and'test2' for each'id' in Table 1 below, calculate the total, and finally calculate the average of all tests.

Table1

id testscore flag
A 2 test1
A 3 test2
A Four test1
A 2 test1
B 3 test2
B 2 test1
B Four test2

The result I want to output

id test1score test2score avgscore
A 8 3 2.75
B 2 7 3

I am thinking of the following SQL statement, and I can get the test1 score, but what should I do if I want to continue to output the test2 score and avgscore?
If anyone can understand it, I would appreciate it if you could let me know.
Thank you.

SELECT id, SUM (testscore) as test1score, SUM (testscore) as test2score, AVG (testscore) as avgscore
 WHERE Table1.flag is'test1'
FROM Table1
ORDER BY id
  • Answer # 1

    It's a good idea to use a crosstab query.

    TRANSFORM Sum (Table1.testscore) AS Sumscore
    SELECT Table1.id, Avg (Table1.testscore) AS avgscore
    FROM Table1
    GROUP BY Table1.id
    PIVOT Table1.flag;

    Result

    id avgscore test1 test2
    A 2.75 8 3
    B 3 2 7

Related articles