Select multiple columns but apply Avg() with only one column?-CodePudding

How to return the program name, campus name, tuition fee of the program, and the average tuition fee for all the programs that are taught on the each campus?

CREATE TABLE Campus
(
    Campus_ID INTEGER NOT NULL,
    Campus_Name    varchar(24),
    Campus_Address varchar(24),
    Campus_City varchar(12)
);

CREATE TABLE Program
(
    Program_ID INTEGER NOT NULL,
    Program_Name varchar(24),
    Program_Description varchar(2000),
    Tuition_Fess numeric(7,2),
    Program_Coordinator_ID INTEGER,
    Campus_ID INTEGER
);

This is what I expected but didn't work since group by needs every columns that being used.

SELECT 
Program.Program_Name,
Campus.Campus_Name,
Program.Tution_Fees,
AVG(Program.Tution_Fees) AS AVERAGE
FROM Program
INNER JOIN Campus
ON Program.Campus_ID=Campus.Campus_ID
GROUP BY Campus.Campus_Name;

CodePudding user response：

You should use something called 'window functions'. They're similar to 'aggregate functions', but the act on every row (partitioning by some field value). I'm sure you will find out how to do it if you check the documentation. You're code will end up looking something like:

SELECT Program.Program_Name,
    Campus.Campus_Name,
    Program.Tution_Fees,
    AVG(Program.Tution_Fees) OVER(PARTITION BY Campus_ID) AS CAMPUS_AVERAGE
FROM Program
    INNER JOIN Campus
    ON Program.Campus_ID=Campus.Campus_ID
GROUP BY Campus.Campus_Name;