How to count rows in one table based on another table in mysql

Question

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,'%)

Any ideas?

Prahalad Gaggar · Accepted Answer · 2013-06-20 17:19:04Z

7

SELECT d.abbreviation, COUNT(*) num
FROM departments d
INNER JOIN courses c ON c.section LIKE CONCAT(d.abbreviation, "%")
GROUP BY d.abbreviation

Sql Fiddle

edited Jun 20, 2013 at 17:19

Prahalad Gaggar

11.6k17 gold badges58 silver badges73 bronze badges

answered Jun 20, 2013 at 16:50

Steve Robbins

13.8k12 gold badges81 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zach Hall Over a year ago

It works! And in a lot less code than I was expecting. And in a lot less time than I expected too. Thanks!!

Edd · Accepted Answer · 2013-06-20 17:17:19Z

0

Give this a shot:

select
    d.abbreviation,
    count(d.abbreviation) count
from
    departments d
inner join
    courses c
on (LOCATE(d.abbreviation,c.section) <> 0)
group by d.abbreviation;

answered Jun 20, 2013 at 17:17

Edd

8,63017 gold badges51 silver badges76 bronze badges

1 Comment

Edd Over a year ago

Actually I prefer Steve's answer

Prahalad Gaggar · Accepted Answer · 2013-06-20 17:19:34Z

0

I quick solution, but not the best, could be:

SELECT abbreviation, 
(SELECT COUNT(*) FROM courses C WHERE D.abbreviation = SUBSTRING(C.abbreviation, 0, 3)) AS c 
FROM departments D;

edited Jun 20, 2013 at 17:19

Prahalad Gaggar

11.6k17 gold badges58 silver badges73 bronze badges

answered Jun 20, 2013 at 16:54

Pablo Francisco Pérez Hidalgo

27.8k8 gold badges39 silver badges64 bronze badges

Comments

Heshan Sandeepa · Accepted Answer · 2018-09-06 10:04:05Z

0

Try this

SELECT d.abbreviation, COUNT(*) num
FROM departments d
INNER JOIN courses c ON c.section LIKE CONCAT(d.abbreviation, "%")
GROUP BY d.abbreviation

edited Sep 6, 2018 at 10:04

Heshan Sandeepa

3,7172 gold badges39 silver badges49 bronze badges

answered Sep 6, 2018 at 7:16

reshma

1

1 Comment

DaveyDaveDave Over a year ago

Welcome to StackOverflow! It seems like you're asking a follow-up question here, rather than giving a new answer. If that's the case, you should ask a whole new question, not post it as an answer here.

Collectives™ on Stack Overflow

How to count rows in one table based on another table in mysql

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related