CODEWITHSUNDEEP
sqlms-access
ChaCol
ChaCol

Reputation: 223

How to add two queries together in one table?

I have two SQL queries that I want to add together on the same table. I want display a table that shows the JuniorCount, SeniorCount, and DeptID.

Right now I have the queries that count the juniors/senior with dept class ECE but I'm not sure how to add them together to display in separate columns as well as the deptid.

SELECT COUNT(*) as JuniorCount
FROM (SELECT Class, DeptID FROM tblStudent WHERE Class = "Junior" AND DeptID = "ECE")


SELECT COUNT(*) as SeniorCount
FROM (SELECT Class, DeptID FROM tblStudent WHERE Class = "Senior" AND DeptID = "ECE")

Upvotes: 0

Views: 185

Answers (2)

Madhivanan
Madhivanan

Reputation: 13700

This is what you need

SELECT DeptID,
sum(case when Class = 'Junior' then 1 else 0 end) as JuniorCount,
sum(case when Class = 'Senior' then 1 else 0 end) as SeniorCount
FROM 
tblStudent WHERE DeptID = 'ECE'
Group by DeptID

In MS ACCESS

SELECT DeptID,
sum(IIF(Class = 'Junior' ,1,0)) as JuniorCount,
sum(IIF(Class = 'Senior' ,1,0)) as JuniorCount
FROM 
tblStudent WHERE DeptID = 'ECE'
Group by DeptID

Upvotes: 6

Janek
Janek

Reputation: 3242

You need to use GROUP BY to get the sums per Class.

SELECT Class, COUNT(*)
FROM tblStudent
WHERE Class IN('Junior', 'Senior')
AND AND DeptID = "ECE"
GROUP BY Class

(The CLASS IN part can be omitted if you only have Juniors and Seniors in the table.)

Upvotes: 2

Related Questions