column " " must appear in the GROUP BY clause or be used in an aggregate function

用了聚集函数 前面要select的列必须出现在聚集函数或者groupby里，不然它不知道怎么分配
查询大于平均WEIGHT的零件，列出他们的供应商代码（SNO），零件代码（PNO），工程代码（JNO），供应数量（QTY）

insert into P(Pno,Pname,color,weight)
values
('P1','螺母','红',12),
('P2','螺栓','绿',17),
('P3','螺丝刀','蓝',14),
('P4','螺丝刀','红',14),
('P5','凸轮','蓝',40),
('P6','齿轮','红',30);

insert into SPJ(Sno,Pno,Jno,QTY)
values
('S1','P1','J1',200),
('S1','P1','J3',100),
('S1','P1','J4',700),
('S1','P2','J2',100),
('S2','P3','J1',400),
('S2','P3','J2',200),
('S2','P3','J4',500),
('S2','P3','J5',400),
('S2','P5','J1',400),
('S2','P5','J2',100),
('S3','P1','J1',200),
('S3','P3','J1',200),
('S4','P5','J1',100),
('S4','P6','J3',300),
('S4','P6','J4',200),
('S5','P2','J4',100),
('S5','P3','J1',200),
('S5','P6','J2',200),
('S5','P6','J4',500);

1）查询大于平均WEIGHT的零件，列出他们的供应商代码（SNO），零件代码（PNO），工程代码（JNO），供应数量（QTY）

select sno,spj.pno,jno,qty from spj,p where spj.pno=p.pno and weight>(select avg(weight) from p)
select sno,pno,jno,qty from spj where pno in(select pno from p  where weight>(select avg(weight) from p))

错误

select sno,spj.pno,jno,qty from spj,p where spj.pno=p.pno  having weight>(select avg(weight) from p) 

错 having要和group by 一起用