'Get the each count of data by combining two tables in MySQL

I want to get the each count of data by combining two tables in MySQL. This is the scenario I have following tables. emp_tab(name, dept_id ) and dept_tab(dept_id, dept_name). I want to write a query to show the number of employees in each department with the department name.

tried code:

SELECT dept_tab.dept_name, number
FROM emp_tab
INNER JOIN dept_tab ON emp_tab.dept_id=dept_tab.dept_id;

My try is not successful. Can you please show me how can I solve this. I am beginner to MySQL

mysql


Solution 1:[1]

Two things:

  1. You need to use a group by and count function
  2. Your join was joining an invalid table
SELECT dept_tab.dept_name, COUNT(*) as number
FROM emp_tab
INNER JOIN dept_tab ON emp_tab.dept_id=dept_tab.dept_id
GROUP BY dept_tab.dept_name

Solution 2:[2]

You can use JOIN and GROUP BY by dept_name to count number of employees.

In your question, what is Customerstable? I assume that is dept_tab?

SELECT 
    d.dept_name,
    COUNT(d.id) AS cnt
FROM
    dept_tab d 
    LEFT JOIN empt_tab e 
        ON e.dept_id = d.dept_id 
GROUP BY d.dept_name ;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jared Dunham
Solution 2

Related Questions