Can't use group by in SQL Server

Question

I am learning how to use Group By in SQL Server and I am trying to write a Query that would let me get all the information from Alumns in a table in numbers.

My table is like the following:

 Name | Alumn_ID | Course | Credits | Passed Peter 1 Math 2 YES John 2 Math 3 YES Thomas 3 Math 0 NO Peter 1 English 3 YES Thomas 2 English 2 YES John 3 English 0 NO

The result I want is the following one:

Alumn | Total_Credits | Courses | Passed | Not_Passed Peter 5 2 2 0 John 5 2 2 0 Thomas 0 2 0 2

I know that I have to use Group By and COUNT but I'm stuck since I'm a beginner, I really don't know how can I separate Passed and Not_Passed in the result from the PASSED column in the table, thanks in advance

You might want to use case, something like sum(case when Passed = 'YES' then 1 else 0 end) — James Z
– James Z, Commented Apr 20, 2018 at 17:56
you don't have to use GROUP BY, PARTITION BY is another option. Show us an attemp and we'll help! — Kevin
– Kevin, Commented Apr 20, 2018 at 17:58

kjmerf · Accepted Answer · 2018-04-20 18:00:27Z

3

SELECT t.id, t.name AS alum, SUM(credits) AS total_credits, COUNT(*) AS courses, SUM(CASE WHEN Passed = 'YES' THEN 1 ELSE 0 END) AS Passed, SUM(CASE WHEN Passed = 'NO' THEN 1 ELSE 0 END) AS Reprobated FROM t GROUP BY t.id, t.name

I assume reprobated means not passed.

edited Apr 20, 2018 at 18:00

answered Apr 20, 2018 at 17:59

kjmerf

4,3853 gold badges24 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user7396598 Over a year ago

I would suggest grouping by the Id and not the name.

kjmerf Over a year ago

Fair. The OP wants the name in the output though, so perhaps both.

Bacon Bits Over a year ago

@user7396598 If t.name is going to be in the output, he must group by t.name in SQL Server. If not, he'll get the error "Column 't.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." SQL Server doesn't do any internal checks to see if a query is implicitly deterministic; it just errors when it isn't explicitly so.

user7396598 Over a year ago

You should use Id and Name. Name is not going to be unique in any real world system. I'm glad kbball got it despite my poor wording.

jean · Accepted Answer · 2018-09-26 11:31:31Z

The example below will do that like you solicited.

create table Alumns ( Name varchar(30) not null ,Alumn_Id int not null ,Course varchar(30) not null ,Credits int not null ,passed varchar(3) not null ) GO insert into Alumns (Name, Alumn_ID, Course, Credits, Passed) values ('Peter', 1, 'Math', 2, 'YES') ,('John', 2, 'Math', 3, 'YES') ,('Thomas', 3, 'Math', 0, 'NO') ,('Peter', 1, 'English', 3, 'YES') ,('John', 2, 'English', 2, 'YES') ,('Thomas', 3, 'English', 0, 'NO') GO select al.Alumn_Id,al.Name , Sum(al.Credits) as [Total Credits] , Count(al.Course) as Courses , Sum(case al.passed when 'YES' then 1 else 0 end) as Passed , Sum(case al.passed when 'NO' then 1 else 0 end) as [Not Passed] from dbo.Alumns al group by al.Alumn_Id, al.Name

but note you will get an error because you data is incorrect. Look at your own example where John and Peter are with wrong Ids for the Math/English rows.

That way you will never end with the correct result and that's why it's a good practice to group by Ids.

Edit I see you corrected your example data yes that way will fetch the exact results you want.

DxTx · Accepted Answer · 2018-04-20 19:42:41Z

You can separate Passed and Not_Passed using a CASE function.

SELECT MAX([name]) AS [Name], SUM(Credits) AS Total_Credits, COUNT(Course) AS Courses, SUM(CASE WHEN Passed='Yes' THEN 1 ELSE 0 END) AS Passed, SUM(CASE WHEN Passed='No' THEN 1 ELSE 0 END) AS Not_Passed FROM TableName GROUP BY Alumn_ID

However, I do not think that values of your tables (both table) are correct. Please check them again. For example, according to your table, John has two Alumn_IDs (both 2 and 3). If these are two different Johns, then your desired outcome should be changed.

Result

+--------+---------------+---------+--------+------------+ | Name | Total_Credits | Courses | Passed | Not_Passed | +--------+---------------+---------+--------+------------+ | Peter | 5 | 2 | 2 | 0 | | John | 3 | 1 | 1 | 0 | | Thomas | 2 | 3 | 1 | 2 | +--------+---------------+---------+--------+------------+

Collectives™ on Stack Overflow

Can't use group by in SQL Server

3 Answers 3

4 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Related