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.
The result I want to output
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.
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;
id avgscore test1 test2 A 2.75 8 3 B 3 2 7
- i want to change the row value using the value of the previous row in python: pandasdataframe
- unable to get indicator value using icustom function in mql4
- i want to get the csv value correctly using php
- java total value aggregation of unspecified number of people
- [python] i want to add a column using the for statement in sqlite and insert data in the last column
- php - in a form using flatpickr, the value disappears when historyback() is done from the confirmation screen
- html - how to calculate the total value of columns stored in db
- vba - cannot store the total value extracted by the auto filter in a variable
- php - i want to define a condition by the value of the acf field in wp_query
- set the value to the argument using java prepared statement multiple
- array - when i receive the value sent from vuejs using axios with laravel, it becomes "[object object]"
- python - i want to calculate the total value and maximum value for each time zone that is pandas
- php - how to add or condition to laravel validation rule? ok if any one value is entered
- php - i want to use it to retrieve the value sent by laravel using the index obtained by turning v-for
- java - i want to sort the variables of list using multiple value values of map
- php - // use the for statement to display the total value from 1 to 10000 → why is "$i = 0"
- sqlite : How to choose the values that are in the same table, but not in another?
- c# : The query runs in Access, but doesn't work when executed from c code #
- Select from SQLite a string where the column coincides with the number in the list. Python
- sql : Create a request, as a result of which you will receive a list of actors' names and the number of films in which every act
- SQL query, find duplicates in a row
- SQL duplicate rows when joining multiple tables
- python : How to check for missing data in the table?
- How to add items to cart in sql?
- java : Big query problem in SQLite3 via hibernate
- SQL Calculate Case in Case