The select s.s id, sname, count (cid), sum (score) from Student as s left join SC on s.s id=SC. Sid group by SC. Sid. There will be a
ERROR 1055 (42000) : # 1 Expression of the SELECT list is not in GROUP BY clause and the contains nonaggregated column 'shop. The s.s id which is not functionally dependent on the columns in GROUP BY clause. This is incompatible with sql_mode=only_full_group_by
But with this you can
Select sname, r. * from (select the sid, count (cid), sum (score) from SC group by SC. Sid) as r left join Student as s on r.s id=s.s id;
Why is this?

CodePudding user response:

Aggregation function, if the field is not statistical column, must be in the group by field

CodePudding user response:

Must want to group aggregation function,
If written in the subquery, you don't need to add a layer of the select,

CodePudding user response:

 
- aggregation function in addition to and Group By using together with Over a window function with the use of 
Example - 

The select s.s id, sname, 
Count (cid) over (partition by SC. Sid order by s.s id), 
The sum (score) over (partition by SC. Sid order by s.s id) 
The from Student as s left join SC on s.s id=SC. Sid 

CodePudding user response:

reference 1/f, February 16 response:

aggregation function, if the field is not statistical columns, you must put behind group by

that is if you use the first method of how to write (not the subquery)? thank you

CodePudding user response:

reference morliz son hin reply: 3/f

aggregation function must be group,
If written in the subquery, you don't need to add a layer of the select,

didn't quite understand the meaning of the first half of the sentence, if you use the first method to write what to write? thank you

CodePudding user response:

 
The select s.s id 
, sname 
, and count (cid) 
, the sum (score) 
The from Student as s 
Left the join SC on s.s id=SC. Sid 

Group by SC. Sid 
//sid and sname are unable to heavy, lead to an error 
Go 

Select sname 
, r. * 
The from (select the sid, count (cid), sum (score) from SC group by SC. Sid) as r 
Left the join Student as s on r.s id=s.s id; 
//advantages in child language, do group collection, this subset as the from table again, and again the original table, can do not need group again, because a subset have to heavy,