How to count two different things in a query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to count two different things in a query?

Author

Topic

SQLFan90
Starting Member

2 Posts

Posted - 2012-03-18 : 18:07:10

I was given this homework problem, and after trying a few different things I am stuck. This is the database we are using:
http://authors.phptr.com/rischert/documents/AppendixD.pdf

Here is the problem:

Create a query that lists location, number of sections taught in that location and number of
students enrolled in courses at that location. Sort by location.

Basically, I need to have two count all's in the same query, and I was thinking of using set operators to do the job. However, when I write the following query, the data is right, but it is under one column:

SELECT location, COUNT (*) AS Sections
FROM section
GROUP BY location
UNION ALL
SELECT location, COUNT (*) AS Students
FROM Student st, enrollment e, section se
WHERE st.student_id = e.student_id
AND e.section_id = se.section_id
GROUP BY location
ORDER BY 1;

Basically, the student data is there, but it is under the section column. I would appreciate help on this, I have a hard time figuring out what I am doing wrong. Thanks in advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-19 : 00:29:44

its quite straightforward and requires a straight join between ENROLLMENT and SECTION tables and applying group over location

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SQLFan90
Starting Member

2 Posts

Posted - 2012-03-19 : 04:00:24

That is what I did, I realized I do not need the student table in the second part of the query, but I still get the sections and students as one column, while I need them as separate columns. Any advice on how to achieve that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-19 : 04:16:01

Tried this?


SELECT		sec.Location,
		COUNT(DISTINCT stu.Student_ID) AS Students,
		COUNT(DISTINCT sec.Section_ID) AS Sections
FROM		dbo.Section AS sec	
LEFT JOIN	dbo.Enrollment AS enr ON enr.Section_ID = sec.Section_ID
LEFT JOIN	dbo.Student AS stu ON stu.Student_ID = enr.Student_ID
GROUP BY	sec.Location
ORDER BY	sec.Location

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources