Re: Can I get this all in one query? - Mailing list pgsql-sql

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,
> );