CODEWITHSUNDEEP
sqljoincount
T3nt4c135
T3nt4c135

Reputation: 112

How to use multiple COUNT statements in SQL sharing the same GROUP BY?

I have the following statement which outputs the correct data

 SELECT COUNT (Section_ID) AS Sections
 FROM Section
 GROUP BY Location;

And this statement which also outputs the correct data

 SELECT COUNT (e.Section_ID) AS Students
 FROM Section s, Enrollment e
 WHERE s.Section_ID = e.Section_ID
 GROUP BY Location;

However, when I put these statements together like so

 SELECT Location, COUNT(s.Section_ID) AS Sections, COUNT (e.Section_ID) AS Students
 FROM Section s, Enrollment e
 WHERE s.Section_ID = e.Section_ID
 GROUP BY Location;

Sections and Students now share the same data. How can I combine these statements together?

Upvotes: 1

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

First, never use commas in the FROM clause. Always use proper, explicit JOIN syntax.

Second, remember what COUNT() does. It counts the number of non-NULL values.

One way to do what you want is to use COUNT(DISTINCT):

SELECT Location, COUNT(DISTINCT s.Section_ID) AS Sections,
       COUNT(DIStINCT e.Student_Id) AS Students
FROM Section s JOIN
     Enrollment e
     ON s.Section_ID = e.Section_ID
GROUP BY Location;

I have to guess on what the primary key is for Enrollment. I'm guessing Student_ID.

Upvotes: 1

Related Questions