CODEWITHSUNDEEP
mysqlsqlselectgroup-by
Zexi Gong
Zexi Gong

Reputation: 189

How to solve this GROUP BY question in MySql?

My table is like named 'score':

Table: score

And I want to query all cno and its corresponding average degree, which has at least 5 student(sno means student number) and starts with 3.

I have tried the following query sentence:

select cno,avg(degree) from score where cno in (select cno from score group by cno  HAVING count(1) > 5 ) and cno like '3%';

However, it throws me an error.

My MySql version is 8.0.18

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'practice01.score.Cno' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Error code 1055.

Upvotes: 1

Views: 109

Answers (3)

shahin mahmud
shahin mahmud

Reputation: 945

select cno, avg(degree) from score 
group by cno HAVING count(cno) > 5 ) and cno like '3%';

after doing group by you can query aggregate like count, sum etc on that grouped up rows in having clause

Upvotes: 2

Gauravsa
Gauravsa

Reputation: 6524

You can simply do this:

select cno, avg(degree) from score 
where cno like '3%' 
group by cno  
HAVING count(cno) >= 5

The reason you are getting this error is because when MySql's only_full_group_by mode is on, strict ANSI SQL rules will apply when using GROUP BY. What this means is that doing group by with one column, you can select aggregate function of other column.

"Aggregates" of other columns means using an aggregate function such as MIN(), MAX(), or AVG() with another column.

Hope this helps.

Since, you are using group by in subquery but not in outer query, you get the error.

Upvotes: 3

Mureinik
Mureinik

Reputation: 311393

You don't need a subquery there - you can apply the having condition on the same query:

SELECT   cno, AVG(degree)
FROM     score
WHERE    cno LIKE '3%'
GROUP BY cno
HAVING   COUNT(*) >= 5

Upvotes: 2

Related Questions