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.


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


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

Related articles