sql not a group by expression, not able to join 3 tables-CodePudding

im trying to connect 3 tables together, it works just fine however when i try to group them by a certain column it shows me not a group by expression:

my tables:

create table userss (
    username varchar2(25)not null , 
    password varchar2(25) ,
    userTypeID number(8) not null,
    gender char(1), 
    dateCreated date,
    lname varchar2(10),
    fname varchar2(10),
    constraint username1_pk primary key (username),
    CONSTRAINT gender1_ck CHECK (gender='M' or gender='F'),
    constraint userTypeID_fk foreign key(userTypeID) references userType (userTypeId)
    );

create table doctor (
    docID number(8) not null , 
    docBioData varchar2(50),
    username varchar2(25)not null , 
    SpecID number(3),
    post_id number(5) not null , 
    constraint docID_pk primary key(docID),
    constraint username4_fk foreign key (username) references userss(username),
    constraint specID2_fk foreign key (specID) references speciality(specID),
    constraint post_id_fk foreign key (post_id) references positions(post_id)
);

create table rating (
    ratID number(10) not null , 
    rat_date date,
    rat_comment varchar2(100),
    rat_rating number(1) not null, 
    docID number(8) not null , 
    patID number(8) not null, 
    constraint ratID_pk primary key(ratID),
    constraint docID_fk foreign key (docID) references doctor(docID),
    constraint patID2_fk foreign key (patID) references patient(patID),
    constraint rating_ck check (rat_rating between 1 and 5)
);

and my code is:

select d.docid, (fname|| ' '|| lname) "Doctor Name", count(r.rat_rating), avg(r.rat_rating)
from userss u, doctor d, rating r
where u.username = d.username and d.docid = r.docid and rat_date > TO_DATE('01-JAN-2020', 'DD-MON-YYYY')
group by d.docid
order by d.docid desc;

CodePudding user response：

In your SELECT there is 2 columns before the COUNT and the AVG, therefore you have to GROUP BY those 2 columns. Depending on your version, you cannot group by on an alias, you would need to encapsulate your first query.

select id, Doctor_Name, COUNT(rat_rating), AVG(rat_rating) from (
    select d.docid AS id, (fname|| ' '|| lname) AS Doctor_Name, r.rat_rating, r.rat_rating
    from userss u, doctor d, rating r
    where u.username = d.username and d.docid = r.docid and rat_date > TO_DATE('01-JAN-2020', 'DD-MON-YYYY')
) A
group by id, Doctor_Name
order by id desc

You could on the other hand delete the "Doctor Name" column from your query, and it should work