0

So, I have this kind of table:

My table

I want to Group the id_class, and count the id_student like this:

Table that I wanted

I used this query:

SELECT id, id_class, COUNT(id_student) FROM tbl_school_years GROUP BY id_class

but I got this error:

Failed to run sql query: column "tbl_school_years.id" must appear in the GROUP BY clause or be used in an aggregate function

Any solution? Any help will be appreciated

Raka M.A
  • 97
  • 1
  • 8
  • Why in your expected output `id` in first row 1 and not let's say 2? – markalex Mar 23 '23 at 23:21
  • Please find a basic SQL tutorial. Proper use of SELECT with GROUP BY is basic SQL functionality, and learning the basics now will save you a lot of frustration going forward. Also, please do a thorough search for the error message before asking a question, as chances are quite good that it has been asked about before here. SO should be your **last resort** after you've exhausted all other means of finding an answer yourself, and not doing a search is basic effort you should make. – Ken White Mar 24 '23 at 00:08
  • Does this answer your question? [must appear in the GROUP BY clause or be used in an aggregate function](https://stackoverflow.com/questions/19601948/must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function) – Ken White Mar 24 '23 at 00:08

1 Answers1

1

The column id could not be in the select clause since it is not on group by clause

We need at first to get id_class and the total_student using :

SELECT id_class, COUNT(id_student) as total_student
FROM tbl_school_years 
GROUP BY id_class

Then we join this subquery with tbl_school_years to get all the possible ids, then using group by we get the smallest or the biggest id using min or max functions

with cte as (
  select t.id, s.*
  from tbl_school_years t
  inner join (
    SELECT id_class, COUNT(id_student) as total_student
    FROM tbl_school_years 
    GROUP BY id_class
  ) as s on s.id_class = t.id_class
)
select min(id), id_class, total_student
from cte
group by id_class, total_student

Demo here

SelVazi
  • 10,028
  • 2
  • 13
  • 29