CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Dog
Dog

Reputation: 115

Can't use group by in SQL Server

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

Upvotes: 1

Views: 2220

Answers (3)

jean
jean

Reputation: 4350

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.

Upvotes: 1

DxTx
DxTx

Reputation: 3357

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 |
+--------+---------------+---------+--------+------------+

Upvotes: 0

kjmerf
kjmerf

Reputation: 4335

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.

Upvotes: 3

Related Questions