Try to make a view but have a problem ORA-00979: not a GROUP BY expression

Question

So, i'm trying to make a view in sql

    CREATE VIEW VIEW_PROJECT 
AS SELECT Pname, Dname, COUNT(W.Essn) as Numb_Employees, SUM(W.Hours) as Total_Hours 
FROM PROJECT P, DEPARTMENT D, WORKS_ON W 
WHERE P.Dnum=D.Dnumber AND P.Pnumber=W.Pno 
GROUP BY Pno;

Then an error occured "ORA-00979: not a GROUP BY expression"

Could you tell me how to fix the query?

Answer 1

You need to aggregate by the unaggregated columns n the select. And use JOIN!

CREATE VIEW VIEW_PROJECT AS
    SELECT p.Pname, d.Dname, COUNT(W.Essn) as Numb_Employees, SUM(W.Hours) as Total_Hours 
    FROM PROJECT P JOIN
         DEPARTMENT D
         ON P.Dnum = D.Dnumber JOIN
         WORKS_ON W 
         ON P.Pnumber = W.Pno 
GROUP BY Pname, Dname;