How to count rows of a column by depending on same values of another column?

Question

I have five columns in a table called tbl_enrolments(id, student_id, semester_id, unit_id, date). Here I just need to count the number of same unit_id according to same semester_id from this table. This is how I've tried so far..

SELECT e.unit_id, COUNT(e.unit_id) as count, 
       u.unit_title, 
       u.unit_name, 
       s.sem_name 
FROM tbl_enrolments e
INNER JOIN tbl_unit_of_study u
    ON e.unit_id = u.id
INNER JOIN tbl_semesters s
    ON e.semester_id = s.id
GROUP BY e.unit_id

Here this query is counting all same unit_id from all different semester_id. But I need to count all same unit_id with only same semester_id. How can I do that?

My table is like this:

Replace the screenshot with CREATE TABLE + INSERT INTO scripts, and show desired answer for this data strictly. Additionally - specify MySQL version. — Akina
– Akina, Commented May 15, 2020 at 6:04
I just need to count the number of same unit_id according to same semester_id from the above table. How can I do that? — Al Arefin
– Al Arefin, Commented May 15, 2020 at 6:24
@AlArefin Have you tried something like WHERE semester_id = x? have a look: sqlfiddle.com/#!9/8bf7e3/5 — F. Müller
– F. Müller, Commented May 15, 2020 at 6:37
@F. Müller thanks for reply. How can I use WHERE, because there's not only one semester_id. There are multiple semester_id and I have to count all unit_id according to all same semester_id. — Al Arefin
– Al Arefin, Commented May 15, 2020 at 6:55
@AlArefin "But I need to count all same unit_id with only same semester_id. How can I do that?" I am a bit confused ... you mean: "select all the unique unit_ids for a given semester?" Like all unique unit_ids for semester 1 for example? — F. Müller
– F. Müller, Commented May 15, 2020 at 7:10

Dusayanta Prasad · Accepted Answer · 2020-05-15 07:43:03Z

Check if this is what you are looking for and if it helps you-

mysql> select semester_id, count(unit_id) from tbl_enrolments group by semester_id;
+-------------+----------------+
| semester_id | count(unit_id) |
+-------------+----------------+
|           2 |              5 |
|           1 |              8 |
|          12 |              2 |
+-------------+----------------+
3 rows in set (0.00 sec)

mysql> select semester_id, unit_id, count(unit_id) from tbl_enrolments group by semester_id, unit_id;
+-------------+---------+----------------+
| semester_id | unit_id | count(unit_id) |
+-------------+---------+----------------+
|           2 |       3 |              3 |
|           2 |       1 |              2 |
|           1 |       2 |              2 |
|           1 |       6 |              2 |
|           1 |       4 |              4 |
|          12 |       1 |              1 |
|          12 |       6 |              1 |
+-------------+---------+----------------+
7 rows in set (0.00 sec)

mysql> select semester_id, unit_id, count(unit_id) from tbl_enrolments group by semester_id, unit_id having semester_id = 2;
+-------------+---------+----------------+
| semester_id | unit_id | count(unit_id) |
+-------------+---------+----------------+
|           2 |       3 |              3 |
|           2 |       1 |              2 |
+-------------+---------+----------------+
2 rows in set (0.00 sec)

mysql> select semester_id, unit_id, count(unit_id) from tbl_enrolments group by semester_id, unit_id having semester_id = 2 and unit_id = 3;
+-------------+---------+----------------+
| semester_id | unit_id | count(unit_id) |
+-------------+---------+----------------+
|           2 |       3 |              3 |
+-------------+---------+----------------+
1 row in set (0.00 sec)

+1 You were faster. I think it is option 2 that he was looking for. :) Here I have created a simple db-fiddle: db-fiddle.com/f/qqA7yWFudT8JUiNa4DMaPG/0 for those who want to play around.

Collectives™ on Stack Overflow

How to count rows of a column by depending on same values of another column?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related