CODEWITHSUNDEEP
sqlsql-server
Ryan
Ryan

Reputation: 20116

LEFT JOIN How to use group by for multiple matches and return count of one column data

I have two tables Student and Grade, when I use LEFT JOIN, if one Student matches multiple rows in Grade, it will all return so the total count is not correct. Below is my structure:

Student:

[OBJECT_ID] [int] NOT NULL,

Grade:

[ID][int] NOT NULL,
[OBJECT_ID] [int] NOT NULL,
[LEVEL] [int] NOT NULL

I want to get each student 's best grade (1,2,3 for 1 is the best) and calculate the amount of each level

select isnull(LEVEL,0) LEVEL ,count(*) as TOTAL_NUM from
       (select * from Student obj) T1

LEFT JOIN 
       (select  ID,OBJECT_ID,LEVEL from Grade) T2 
on T1.OBJECT_ID = T2.OBJECT_ID
group by LEVEL

It works when there are only one match in Grade table. However, if one student has multiple grades, then it will return multiple student rows so the result is not correct.

For example, if one student has 1,2,3 grades, it only considers the grade 1 to count.

I just need to return all amount of each level and the SUM of each TOTAL_NUM equals to the rows of Student table.

Upvotes: 0

Views: 1586

Answers (1)

Sander
Sander

Reputation: 4042

You can do a double aggregation. A first one to determine the level for each student (min()) and a second one to count the number of students on each level (count()).

Sample data

create table student
(
  [OBJECT_ID] [int] NOT NULL
);

insert into student (object_id) values
(1),
(2),
(3);

create table grade
(
  [ID][int] NOT NULL,
  [OBJECT_ID] [int] NOT NULL,
  [LEVEL] [int] NOT NULL
);

insert into grade (id, object_id, level) values
(100, 1, 1),
(101, 1, 2),
(102, 1, 2), -- student 1 has 1x level 1 and 2x level 2 grades
(200, 2, 1),
(201, 2, 3), -- student 2 has 1x level 1 and 1x level 3 grades
(300, 3, 2),
(301, 3, 2),
(302, 3, 3); -- student 3 has 2x level 2 and 1x level 3 grades

With this sample data there are 2 "level 1" students and 1 "level 2" student.

Version with CTE

with cte as
(
  select min(g.level) as level
  from grade g
  group by g.object_id
)
select cte.level,
       count(1) as total_num
from cte
group by cte.level
order by cte.level;

Version with subquery

select x.level,
       count(1) as total_num
from (
       select min(g.level) as level
       from grade g
       group by g.object_id
     ) x
group by x.level
order by x.level;

Result

level total_num
----- ---------
1     2
2     1

Fiddle to see everything in action.

Upvotes: 1

Related Questions