CODEWITHSUNDEEP
mysqlsqlruby-on-rails
opticon
opticon

Reputation: 3614

SELECT name and rowcount?

I've bloody well done this before, but I plumb forget how.

I have a table of Schools, each with an ID and name.

I have another table of Students, each with a school_id foreign key.

I want to write a query that'll return a list of schools and the count of their students I have on record; something like this:

School1 - 319
School2 - 166
School3 - 120

Bonus if it can be done elegantly in Rails without resorting to raw SQL!

Upvotes: 1

Views: 55

Answers (3)

al3xjohnson
al3xjohnson

Reputation: 78

Give this a shot:

SELECT s.schoolName, COUNT(st.id) 
FROM Schools s
JOIN Students st
  ON st.school_id = s.school_id
GROUP BY s.school_id

Upvotes: 0

Sébastien
Sébastien

Reputation: 12139

No rails points for me:

SELECT
school_name, count(*)

FROM
schools

LEFT JOIN
students
USING(school_id)

GROUP BY
school_id

Upvotes: 0

Filipe Silva
Filipe Silva

Reputation: 21657

In SQL you can just do:

SELECT s.ID, COUNT(*)
FROM schools s
INNER JOIN students ss ON ss.schoolID = s.id
GROUP BY s.ID

(about the rails part i can't help you, sorry).

Upvotes: 1

Related Questions