I want to use SQL to get only the data that meets the conditions from the following two tables.
Specifically, I would like to get the date and time data from Table 1 and calculate the total value of the corresponding data from Table 2 based on that data.
Using the pair of ‘testdate’ and ‘id’ in Table1 as a key, get the date and time of ‘Start’ and ‘finish’,
I want to find the total of the ‘score’ of the ‘dodate’ in Table 2 included during that date and time.
|2010/5/10||A||2010/5/10 12:10||2010/5/10 15:10|
|2011/6/10||B||2011/6/10 13:00||2011/6/10 18:10|
|2011/6/10||B||2011/6/10 21:00||2011/6/10 23:00|
|2011/10/10||C||2011/10/10 9:10||2011/10/10 17:10|
|2011/10/15||C||2011/10/15 9:10||2011/10/15 17:10|
The result I want to output
select testdate, id, SUM (score) as gouke from table1, table2 ・ ・ ・
I am sorry that the explanation is difficult to understand.
If anyone knows, please let me know.
Answer # 1
With Access SQL, you'll get the desired result below.
SELECT Table1.testdate, Table1.id, Sum (Table2.score) AS gouke FROM FROM Table1 INNER JOIN Table2 ON Table1.id = Table2.id WHERE Table2.dodae Between Table1.Start And Table1.finish GROUP BY Table1.testdate, Table1.id;
- sqlite - [sql] i want to extract the sum of two different conditions in access
- php - i want to extract only the data that meets the multiple conditions specified in sql and display it in the browser
- java - how to create multiple tables with sqlite on android
- please tell me how to create multiple tables in sqlite db on android
- cakephp - data deletion that meets the conditions
- i want to do outer joins from multiple tables vbnet sqlite
- sqlite - how to manage multiple tables in android studio
- ruby - data that meets the conditions cannot be retrieved and displayed with the where method
- python 3x - i want to add data that meets conditions from another data frame
- i want to call records that meet conditions from multiple tables in cakephp3
- mysql - applying conditions to the joined tables while outputting all the data of the main table (if the conditions of the joine
- i want to copy a file with a name that meets the conditions while keeping the directory structure with linux commands
- sqlite - added django database and tables
- mysql - i want to get only the date that has a date for each column and meets certain conditions
- i don't know if the execution result meets the conditions
- python - i want to display/write the line of the csv file that meets the conditions
- sqlite - i want to put two tables together!
- php - i want to get the id of an article that meets the conditions in wordpress
- You need to get SQL code from Oracle SQL Modeler, and insert it in SQLite, for further work with the database
- python : 'NoneType' object is not subscriptable. Help
- 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?
- c# : The query runs in Access, but doesn't work when executed from c code #
- java : Big query problem in SQLite3 via hibernate
- About text type filter operation after changing Access DB to Azure SQL