I have two tables in a MySQL database. The first one has a list of department names.
departments abbreviation | name -------------|------------- ACC | accounting BUS | business ...
The second table has a list of courses with names that contain the department's abbreviation.
courses section | name -------------|------------- ACC-101-01 | Intro to Accounting ACC-110-01 | More accounting BUS-200-02 | Business etc. ...
I'd like to write a query that will, for each row in the
departments table, give me a count of how many rows in the
courses table are like the abbreviation I have. Something such as this:
abbreviation | num -------------|-------------- ACC | 2 BUS | 1 ...
I can do this for one individual department with the query
SELECT COUNT(*) FROM courses WHERE section LIKE '%ACC%' (gives me 2)
Although I could loop through in PHP and do the above query many times, I'd rather do it in a single query. This is the pseudocode I'm thinking of...
SELECT department.abbreviation, num FROM for each row in departments SELECT COUNT(*) AS num FROM classes WHERE section LIKE CONCAT('%',departments.abbreviation,'%)
SELECT d.abbreviation, COUNT(*) num FROM departments d INNER JOIN courses c ON c.section LIKE CONCAT(d.abbreviation, "%") GROUP BY d.abbreviation
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!Donate Us With