Number of columns

Question

I have hava sql as below:

select a.dept, a.name from students a group by dept, name order by dept, name 

And get the result:

 dept name -----+--------- CS | Aarthi CS | Hansan EE | SF EE | Nikke2 

I want to summarize the number of students for each department, as shown below:

 dept name count -----+-----------+------ CS | Aarthi | 2 CS | Hansan | 2 EE | SF | 2 EE | Nikke2 | 2 Math | Joel | 1 

How do I write sql?

Answer 1

Although it seems that you are not showing all the tables, I can only assume that there is a different actual registration table for each student

 select a.Dept, count(*) as TotalStudents from students a group by a.Dept 

If you want the total number of each department to be associated with each student (which does not make sense), you will probably have to do this as ...

 select a.Dept, a.Name, b.TotalStudents from students a, ( select Dept, count(*) TotalStudents from students group by Dept ) b where a.Dept = b.Dept 

My interpretation of your “Name” column is the name of the student, not the name of the actual class instructor, so my sub-choice / connection. Otherwise, like the others, you just need to use COUNT (*) as the third column.

Answer 2

 select a.dept, a.name, (SELECT count(*) FROM students WHERE dept = a.dept) from students a group by dept, name order by dept, name 

This is a somewhat dubious request, since you get duplicate instances of the department. It would be easier to get a list of students, and the department would count individual results. Of course, there may be pragmatic reasons to go the other way, so this is not an absolute rule.

Answer 3

 SELECT dept, name, COUNT(name) as CT from students group by dept, name order by dept, name 

Answer 4

This should do it (I don't have a testing environment in min.)

 select a.dept, a.name, count(a.*) as NumOfStudents from students a group by dept, name order by dept, name 

NTN

Answer 5

Or else just write

 select dept, name, count(name) as nostud from students group by dept, name order by dept, name 

Answer 6

This will give the results requested above.

 select a.dept, a.name, cnt from student a join ( select dept, count(1) as cnt from student group by dept ) b on b.dept = a.dept