Thread: Can I get this all in one query?
I'm designing a database/website that will allow students to "grade" the professors/classes the students attend.
There are eight different "factors" that the students assign grades on. Until tonight I had one table that kept the
scores assigned by students. This table name REVIEW had a field named for each factor. IE:
CREATE TABLE review(
class_uid INTEGER REFERENCES class,
student VARCHAR(20) REFERENCES student,
snooze INTEGER,
professional INTEGER,
personality INTEGER,
grading INTEGER,
effectiveness INTEGER,
knowledge INTEGER,
accent INTEGER,
enthusiasism INTEGER,
uid SERIAL PRIMARY KEY,
UNIQUE(class_uid,student)
)
THis allowed me to write simple queries that would pull averages for a college,department, a course, a professor,
etc. Example query:
//Returns averages in all 8 factors of every department
SELECT crs.dept AS department, ROUND(AVG(CAST(rvw.snooze AS FLOAT)) ,2) AS snooze,
ROUND(AVG(CAST(rvw.professional AS FLOAT)) ,2) AS professional, ROUND(AVG(CAST(rvw.personality AS FLOAT)) ,2) AS
personality, ROUND(AVG(CAST(rvw.grading AS FLOAT)) ,2) AS grading, ROUND(AVG(CAST(rvw.effectiveness AS
FLOAT)),2) AS effectiveness, ROUND(AVG(CAST(rvw.knowledge AS FLOAT)) ,2) AS knowledge,
ROUND(AVG(CAST(rvw.accent AS FLOAT)) ,2) AS accent, ROUND(AVG(CAST(rvw.enthusiasism AS FLOAT)) ,2) AS
enthusiasismFROM review rvw, class cls, course crs WHERE rvw.class_uid=cls.uid AND cls.course_uid=crs.uid GROUP BY
crs.dept;
However, in a developer meating tonight it was decided that the factors (which are in another table in the database)
should not be "hard coded" into the review table, but rather a new table should be created implementin the many to
many relationship. The revised review table(s) looks like so:
CREATE TABLE review
( class_uid INTEGER REFERENCES class, student VARCHAR(30)
REFERENCESstudent, comments TEXT,
uid SERIAL PRIMARY KEY, UNIQUE(class_uid,student)
);
CREATE TABLE review_scores
( review_uid INTEGER REFERENCES review, factor VARCHAR(30)
REFERENCESfactor, score INTEGER,
uid SERIAL PRIMARY KEY, UNIQUE(review_uid,factor)
);
My problem is now I do not know how to write a single query that can pull the average of all 8 factors at once,
grouped my department as above. If it is a specific department it is trivial, but for all departments at once I would
need to use two group by statements in the same SQL query.
I'm sure there is a simple solution, possibly using sub-queries which I'm not very farmiliar with. I am attaching the
database schema below in case it would make things clearer.
TIA!
CREATE TABLE term
( name VARCHAR(30) PRIMARY KEY
);
CREATE TABLE semester
( term VARCHAR(30) REFERENCES term ON UPDATE CASCADE, year INTEGER,
locked BOOLEAN DEFAULT TRUE,
uid SERIAL PRIMARY KEY, UNIQUE(term,year)
);
CREATE TABLE college
( name VARCHAR(30) PRIMARY KEY
);
CREATE TABLE department
( department_id VARCHAR(4) PRIMARY KEY, name VARCHAR(30) NOT NULL, college
VARCHAR(30) REFERENCES college ON UPDATE CASCADE
);
CREATE TABLE degree
( name VARCHAR(30) PRIMARY KEY
);
CREATE TABLE professor
( first_name VARCHAR(30) NOT NULL, last_name VARCHAR(30) NOT NULL, email_address VARCHAR(30),
degree_type VARCHAR(30) REFERENCES degree, undergrad_univ VARCHAR(50), grad_univ
VARCHAR(50),major VARCHAR(20), comment TEXT,
uid SERIAL PRIMARY KEY
);
CREATE TABLE professor_department_link
( prof_uid INTEGER REFERENCES professor, dept VARCHAR(4) REFERENCES
department,UNIQUE(prof_uid,dept)
);
CREATE TABLE email_domain
( domain VARCHAR(20) PRIMARY KEY
);
CREATE TABLE student
( passwd VARCHAR(30) NOT NULL, email_prefix VARCHAR(30) NOT NULL, email_domain VARCHAR(20)
REFERENCES email_domain ON UPDATE CASCADE, authenticated BOOLEAN DEFAULT FALSE,
screen_name VARCHAR(20) PRIMARY KEY, UNIQUE(email_prefix,email_domain)
);
CREATE TABLE course
( dept VARCHAR(4) REFERENCES department ON UPDATE CASCADE, course_number VARCHAR(8),
description VARCHAR(100),
uid SERIAL PRIMARY KEY, UNIQUE(dept,course_number,description)
);
CREATE TABLE class
( course_uid INTEGER REFERENCES course, prof_uid INTEGER
REFERENCESprofessor, semester_uid INTEGER REFERENCES semester,
uid SERIAL PRIMARY KEY, UNIQUE (course_uid, prof_uid, semester_uid)
);
CREATE TABLE review
( class_uid INTEGER REFERENCES class, student VARCHAR(30)
REFERENCESstudent, comments TEXT,
uid SERIAL PRIMARY KEY, UNIQUE(class_uid,student)
);
CREATE TABLE review_scores
( review_uid INTEGER REFERENCES review, factor VARCHAR(30)
REFERENCESfactor, score INTEGER,
uid SERIAL PRIMARY KEY, UNIQUE(review_uid,factor)
);
CREATE TABLE factor
( name VARCHAR(30) PRIMARY KEY, description TEXT, a_description TEXT,
b_description TEXT, c_description TEXT, d_description TEXT, f_description TEXT,
);
I don't have a postgresql instalation right now to do some tests, but maybe it works... SELECT crs.dept AS department, ROUND(AVG(CAST(rvs1.score AS FLOAT)) ,2) AS snooze, ROUND(AVG(CAST(rvs2.score ASFLOAT)) ,2) AS professional, ROUND(AVG(CAST(rvs3.score AS FLOAT)) ,2) AS personality, ROUND(AVG(CAST(rvs4.scoreAS FLOAT)) ,2) AS grading, ROUND(AVG(CAST(rvs5.score AS FLOAT)) ,2) AS effectiveness, ROUND(AVG(CAST(rvs6.score AS FLOAT)) ,2) AS knowledge, ROUND(AVG(CAST(rvs7.score AS FLOAT)) ,2) AS accent, ROUND(AVG(CAST(rvs8.scoreAS FLOAT)) ,2) AS enthusiasism FROM review rvw, class cls, course crs, review_scores rvs1, review_scoresrvs2, .... WHERE rvw.class_uid=cls.uid AND cls.course_uid=crs.uid AND rvw.uid = rvs1.review_uid AND rvs1.factor= "factor_1" AND rvw.uid = rvs2.review_uid AND rvs2.factor = "factor_2" ... GROUP BY crs.dept; HTH, -- Renato Sao Paulo - SP - Brasil rdg@viafractal.com.br > I'm designing a database/website that will allow students to "grade" the professors/classes the students attend. > There are eight different "factors" that the students assign grades on. Until tonight I had one table that kept the > scores assigned by students. This table name REVIEW had a field named for each factor. IE: > > CREATE TABLE review( > class_uid INTEGER REFERENCES class, > student VARCHAR(20) REFERENCES student, > snooze INTEGER, > professional INTEGER, > personality INTEGER, > grading INTEGER, > effectiveness INTEGER, > knowledge INTEGER, > accent INTEGER, > enthusiasism INTEGER, > > uid SERIAL PRIMARY KEY, > UNIQUE(class_uid,student) > ) > > THis allowed me to write simple queries that would pull averages for a college,department, a course, a professor, > etc. Example query: > > //Returns averages in all 8 factors of every department > SELECT crs.dept AS department, > ROUND(AVG(CAST(rvw.snooze AS FLOAT)) ,2) AS snooze, > ROUND(AVG(CAST(rvw.professional AS FLOAT)) ,2) AS professional, > ROUND(AVG(CAST(rvw.personality AS FLOAT)) ,2) AS personality, > ROUND(AVG(CAST(rvw.grading AS FLOAT)) ,2) AS grading, > ROUND(AVG(CAST(rvw.effectiveness AS FLOAT)) ,2) AS effectiveness, > ROUND(AVG(CAST(rvw.knowledge AS FLOAT)) ,2) AS knowledge, > ROUND(AVG(CAST(rvw.accent AS FLOAT)) ,2) AS accent, > ROUND(AVG(CAST(rvw.enthusiasism AS FLOAT)) ,2) AS enthusiasism > FROM review rvw, class cls, course crs > WHERE rvw.class_uid=cls.uid AND cls.course_uid=crs.uid > GROUP BY crs.dept; > > However, in a developer meating tonight it was decided that the factors (which are in another table in the database) > should not be "hard coded" into the review table, but rather a new table should be created implementin the many to > many relationship. The revised review table(s) looks like so: > > CREATE TABLE review > ( > class_uid INTEGER REFERENCES class, > student VARCHAR(30) REFERENCES student, > comments TEXT, > > uid SERIAL PRIMARY KEY, > UNIQUE(class_uid,student) > ); > > CREATE TABLE review_scores > ( > review_uid INTEGER REFERENCES review, > factor VARCHAR(30) REFERENCES factor, > score INTEGER, > > uid SERIAL PRIMARY KEY, > UNIQUE(review_uid,factor) > ); > > My problem is now I do not know how to write a single query that can pull the average of all 8 factors at once, > grouped my department as above. If it is a specific department it is trivial, but for all departments at once I would > need to use two group by statements in the same SQL query. > > I'm sure there is a simple solution, possibly using sub-queries which I'm not very farmiliar with. I am attaching the > database schema below in case it would make things clearer. > > TIA! > > CREATE TABLE term > ( > name VARCHAR(30) PRIMARY KEY > ); > > CREATE TABLE semester > ( > term VARCHAR(30) REFERENCES term ON UPDATE CASCADE, > year INTEGER, > locked BOOLEAN DEFAULT TRUE, > > uid SERIAL PRIMARY KEY, > UNIQUE(term,year) > ); > > CREATE TABLE college > ( > name VARCHAR(30) PRIMARY KEY > ); > > CREATE TABLE department > ( > department_id VARCHAR(4) PRIMARY KEY, > name VARCHAR(30) NOT NULL, > college VARCHAR(30) REFERENCES college ON UPDATE CASCADE > ); > > CREATE TABLE degree > ( > name VARCHAR(30) PRIMARY KEY > ); > > CREATE TABLE professor > ( > first_name VARCHAR(30) NOT NULL, > last_name VARCHAR(30) NOT NULL, > email_address VARCHAR(30), > degree_type VARCHAR(30) REFERENCES degree, > undergrad_univ VARCHAR(50), > grad_univ VARCHAR(50), > major VARCHAR(20), > comment TEXT, > > uid SERIAL PRIMARY KEY > ); > > CREATE TABLE professor_department_link > ( > prof_uid INTEGER REFERENCES professor, > dept VARCHAR(4) REFERENCES department, > UNIQUE(prof_uid,dept) > ); > > CREATE TABLE email_domain > ( > domain VARCHAR(20) PRIMARY KEY > ); > > CREATE TABLE student > ( > passwd VARCHAR(30) NOT NULL, > email_prefix VARCHAR(30) NOT NULL, > email_domain VARCHAR(20) REFERENCES email_domain ON UPDATE CASCADE, > authenticated BOOLEAN DEFAULT FALSE, > > screen_name VARCHAR(20) PRIMARY KEY, > UNIQUE(email_prefix,email_domain) > ); > > CREATE TABLE course > ( > dept VARCHAR(4) REFERENCES department ON UPDATE CASCADE, > course_number VARCHAR(8), > description VARCHAR(100), > > uid SERIAL PRIMARY KEY, > UNIQUE(dept,course_number,description) > ); > > CREATE TABLE class > ( > course_uid INTEGER REFERENCES course, > prof_uid INTEGER REFERENCES professor, > semester_uid INTEGER REFERENCES semester, > > uid SERIAL PRIMARY KEY, > UNIQUE (course_uid, prof_uid, semester_uid) > ); > > CREATE TABLE review > ( > class_uid INTEGER REFERENCES class, > student VARCHAR(30) REFERENCES student, > comments TEXT, > > uid SERIAL PRIMARY KEY, > UNIQUE(class_uid,student) > ); > > CREATE TABLE review_scores > ( > review_uid INTEGER REFERENCES review, > factor VARCHAR(30) REFERENCES factor, > score INTEGER, > > uid SERIAL PRIMARY KEY, > UNIQUE(review_uid,factor) > ); > > CREATE TABLE factor > ( > name VARCHAR(30) PRIMARY KEY, > description TEXT, > a_description TEXT, > b_description TEXT, > c_description TEXT, > d_description TEXT, > f_description TEXT, > );