SQL query that returns aggregated and non-aggregated results

Question

I need to write a query that returns both aggregate and non-aggregate data from a table.

The example below should help clarify what I'm trying to do. I have the following (simplified) tables below:

CREATE TABLE course_group_def ( id PRIMARY SERIAL KEY, name TEXT NOT NULL ); CREATE TABLE student ( id PRIMARY SERIAL KEY, grp_id INTEGER REFERENCES course_group_def(id) ON UPDATE CASCADE, name TEXT NOT NULL, weight float NOT NULL, height float NOT NULL ); 

For argument, suppose that each student belongs to one and only a group of courses. I want to write a query that will return a result set as follows:

student.name, student.weight, weight_apgaw, weight_apgh

Where:

weight_apgaw: - the weight of an individual student, expressed as a percentage of the average weight of the group of courses to which he / she belongs.

weight_apgh: this is the weight of the individual student, expressed as a percentage of the average height of the group of courses to which he / she belongs

I have no idea (well, some idea) on how to write such a request. My approach would be to write two queries and somehow search between the two tables, but this approach seems at best pointless and inefficient.

Can anyone suggest how I can write such an SQL function correctly ?. Ideally, this should be db agnostic. However, I use PostgreSQL 8.4, so if I need to choose between SQL options, this takes precedence.

Answer 1

a query on these lines should start you:

 select s.name , s.weight , ((s.weight/st.avgweight) * 100) as weight_apgaw , ((s.height/st.avgheight) * 100) as weight_aphei from student s join ( select grp_id , avg(weight) as avgweight , avg(height) as avgheight from student group by grp_id ) st on s.grp_id = st.grp_id 

Answer 2

You need to use the window functions - like this:

 select name, weight, height, 100 * weight / avg(weight) over (partition by grp_id) weight_apgaw, 100 * weight / avg(height) over (partition by grp_id) weight_apgh from student