Querying from multiple tables at the same time - related to correlated subqueries
-
I'm trying to extract some data using SQL queries from different tables I have already created. The tables are as follows: CREATE TABLE Students ( Student_ID INTEGER PRIMARY KEY, Enrollment_Year DATE, Course_Current_Status VARCHAR(18), First_Name TEXT, Last_Name TEXT, Gender TEXT, Date_Of_Birth DATE, Email TEXT, CourseCode INTEGER REFERENCES Courses(CourseCode) ); CREATE TABLE Modules ( Module_Code INTEGER PRIMARY KEY, Module_Name TEXT, Module_Credits INTEGER, Module_Level INTEGER, ConvenerID INTEGER REFERENCES Conveners(ConvenerID) ); CREATE TABLE Enrollment ( Marks_Obtained INTEGER, Module_Code INTEGER REFERENCES Modules(Module_Code), Student_ID INTEGER REFERENCES Students(Student_ID), Program_Year_When_Enrolled TEXT, PRIMARY KEY(Module_Code, Student_ID) ); I want to show three columns with my query: Student_ID, Average_Second_Year_Marks, Average_Third_Year_Marks, Overall_Marks What I want to do is extract data for students graduating in 2017 i.e. the Course_Current_Status = 'Graduated-2017'. For second year marks the Enrollment.Program_Year_When_Enrolled = 'Second' And for the third year marks the Enrollment.Program_Year_When_Enrolled = 'Third'. For the overall marks a new column would have to be created by the query i.e. Overall Marks which would be 1/3 of the second year marks and 2/3 of the third year marks. What I'm using is as follows: SELECT Students.Student_ID, AVG (Enrollement.Marks_obtained WHERE Enrollment.Program_Year_When_Enorolled = 'Third' ) AS avg_third_year_marks, AVG (Enrollement.Marks_obtained WHERE Enrollment.Program_Year_When_Enorolled = 'Second' ) AS avg_second_year_marks AVG (Enrollment.Marks_obtained = 1/3 * avg_second_year_marks + 2/3 * avg_third_year_marks) AS Overall_Marks FROM Students LEFT JOIN Enrollment ON Students.Student_ID=Enrollment.Student_ID WHERE Students.Course_Current_Year='Graduated-2017' GROUP BY Students.Student_ID
-
I'm trying to extract some data using SQL queries from different tables I have already created. The tables are as follows: CREATE TABLE Students ( Student_ID INTEGER PRIMARY KEY, Enrollment_Year DATE, Course_Current_Status VARCHAR(18), First_Name TEXT, Last_Name TEXT, Gender TEXT, Date_Of_Birth DATE, Email TEXT, CourseCode INTEGER REFERENCES Courses(CourseCode) ); CREATE TABLE Modules ( Module_Code INTEGER PRIMARY KEY, Module_Name TEXT, Module_Credits INTEGER, Module_Level INTEGER, ConvenerID INTEGER REFERENCES Conveners(ConvenerID) ); CREATE TABLE Enrollment ( Marks_Obtained INTEGER, Module_Code INTEGER REFERENCES Modules(Module_Code), Student_ID INTEGER REFERENCES Students(Student_ID), Program_Year_When_Enrolled TEXT, PRIMARY KEY(Module_Code, Student_ID) ); I want to show three columns with my query: Student_ID, Average_Second_Year_Marks, Average_Third_Year_Marks, Overall_Marks What I want to do is extract data for students graduating in 2017 i.e. the Course_Current_Status = 'Graduated-2017'. For second year marks the Enrollment.Program_Year_When_Enrolled = 'Second' And for the third year marks the Enrollment.Program_Year_When_Enrolled = 'Third'. For the overall marks a new column would have to be created by the query i.e. Overall Marks which would be 1/3 of the second year marks and 2/3 of the third year marks. What I'm using is as follows: SELECT Students.Student_ID, AVG (Enrollement.Marks_obtained WHERE Enrollment.Program_Year_When_Enorolled = 'Third' ) AS avg_third_year_marks, AVG (Enrollement.Marks_obtained WHERE Enrollment.Program_Year_When_Enorolled = 'Second' ) AS avg_second_year_marks AVG (Enrollment.Marks_obtained = 1/3 * avg_second_year_marks + 2/3 * avg_third_year_marks) AS Overall_Marks FROM Students LEFT JOIN Enrollment ON Students.Student_ID=Enrollment.Student_ID WHERE Students.Course_Current_Year='Graduated-2017' GROUP BY Students.Student_ID
You are going to have to split this into a query and a sub query (unless Richard comes in with a CTE). Sub query should be everyting EXCEPT
AVG (Enrollment.Marks_obtained = 1/3 * avg_second_year_marks + 2/3 * avg_third_year_marks) AS Overall_Marks
The averaging variables do not exist in the current structure until AFTER the query is run. Then try: Select * from (put the sub query here) as SQ You can then add the totaling column after the * because the subquery would have been run and the averaging values populated.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
I'm trying to extract some data using SQL queries from different tables I have already created. The tables are as follows: CREATE TABLE Students ( Student_ID INTEGER PRIMARY KEY, Enrollment_Year DATE, Course_Current_Status VARCHAR(18), First_Name TEXT, Last_Name TEXT, Gender TEXT, Date_Of_Birth DATE, Email TEXT, CourseCode INTEGER REFERENCES Courses(CourseCode) ); CREATE TABLE Modules ( Module_Code INTEGER PRIMARY KEY, Module_Name TEXT, Module_Credits INTEGER, Module_Level INTEGER, ConvenerID INTEGER REFERENCES Conveners(ConvenerID) ); CREATE TABLE Enrollment ( Marks_Obtained INTEGER, Module_Code INTEGER REFERENCES Modules(Module_Code), Student_ID INTEGER REFERENCES Students(Student_ID), Program_Year_When_Enrolled TEXT, PRIMARY KEY(Module_Code, Student_ID) ); I want to show three columns with my query: Student_ID, Average_Second_Year_Marks, Average_Third_Year_Marks, Overall_Marks What I want to do is extract data for students graduating in 2017 i.e. the Course_Current_Status = 'Graduated-2017'. For second year marks the Enrollment.Program_Year_When_Enrolled = 'Second' And for the third year marks the Enrollment.Program_Year_When_Enrolled = 'Third'. For the overall marks a new column would have to be created by the query i.e. Overall Marks which would be 1/3 of the second year marks and 2/3 of the third year marks. What I'm using is as follows: SELECT Students.Student_ID, AVG (Enrollement.Marks_obtained WHERE Enrollment.Program_Year_When_Enorolled = 'Third' ) AS avg_third_year_marks, AVG (Enrollement.Marks_obtained WHERE Enrollment.Program_Year_When_Enorolled = 'Second' ) AS avg_second_year_marks AVG (Enrollment.Marks_obtained = 1/3 * avg_second_year_marks + 2/3 * avg_third_year_marks) AS Overall_Marks FROM Students LEFT JOIN Enrollment ON Students.Student_ID=Enrollment.Student_ID WHERE Students.Course_Current_Year='Graduated-2017' GROUP BY Students.Student_ID
I'd recommend normalizing them first, and wait with defining operations until that is completed :thumbsup:
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
I'm trying to extract some data using SQL queries from different tables I have already created. The tables are as follows: CREATE TABLE Students ( Student_ID INTEGER PRIMARY KEY, Enrollment_Year DATE, Course_Current_Status VARCHAR(18), First_Name TEXT, Last_Name TEXT, Gender TEXT, Date_Of_Birth DATE, Email TEXT, CourseCode INTEGER REFERENCES Courses(CourseCode) ); CREATE TABLE Modules ( Module_Code INTEGER PRIMARY KEY, Module_Name TEXT, Module_Credits INTEGER, Module_Level INTEGER, ConvenerID INTEGER REFERENCES Conveners(ConvenerID) ); CREATE TABLE Enrollment ( Marks_Obtained INTEGER, Module_Code INTEGER REFERENCES Modules(Module_Code), Student_ID INTEGER REFERENCES Students(Student_ID), Program_Year_When_Enrolled TEXT, PRIMARY KEY(Module_Code, Student_ID) ); I want to show three columns with my query: Student_ID, Average_Second_Year_Marks, Average_Third_Year_Marks, Overall_Marks What I want to do is extract data for students graduating in 2017 i.e. the Course_Current_Status = 'Graduated-2017'. For second year marks the Enrollment.Program_Year_When_Enrolled = 'Second' And for the third year marks the Enrollment.Program_Year_When_Enrolled = 'Third'. For the overall marks a new column would have to be created by the query i.e. Overall Marks which would be 1/3 of the second year marks and 2/3 of the third year marks. What I'm using is as follows: SELECT Students.Student_ID, AVG (Enrollement.Marks_obtained WHERE Enrollment.Program_Year_When_Enorolled = 'Third' ) AS avg_third_year_marks, AVG (Enrollement.Marks_obtained WHERE Enrollment.Program_Year_When_Enorolled = 'Second' ) AS avg_second_year_marks AVG (Enrollment.Marks_obtained = 1/3 * avg_second_year_marks + 2/3 * avg_third_year_marks) AS Overall_Marks FROM Students LEFT JOIN Enrollment ON Students.Student_ID=Enrollment.Student_ID WHERE Students.Course_Current_Year='Graduated-2017' GROUP BY Students.Student_ID
It's always easier to help sort problems out if you also supply some sample data and expected results. You are using the TEXT datatype for some key data items - this allows up to 2GB of text and is the wrong choice for things like names, emails etc. You also cannot compare or sort easily. Use Varchar instead e.g.
VARCHAR(MAX)(Although that is also overkill, I usually use varchar(50)) You are using WHERE when you should be using CASE WHEN ... END
