A question about the feasibility of a script

Question

Imagine you have a table of employee data that includes each employee's name, job title, and the name of their supervisor. You want to find the total number of employees in each job category.

WITH RECURSIVE job_categories AS (
  SELECT job_title, COUNT(*) AS employee_count
  FROM employee_data
  GROUP BY job_title
  UNION ALL
  SELECT e.job_title, COUNT(*) AS employee_count
  FROM employee_data e
  JOIN job_categories jc ON e.supervisor = jc.job_title
  GROUP BY e.job_title
)
SELECT job_title, SUM(employee_count) AS total_employees
FROM job_categories
GROUP BY job_title;

I saw this code in a tutorial about RECURSIVE clause in SQL. In the JOIN section i don't understood how could it be the comparison between the supervisor name and the job title.. It does not contain the same data. Job title it can be like "developer" and supervisor name it can be something like "Yu Lee".

I will be happy if someone can explain me this QUERY and how it works

Thanks for your time and efforts.

The query is not valid T-SQL syntax (there is no `RECURSIVE` keyword in a CTE definition). I suggest a different tutorial specific to SQL Server. — Dan Guzman, Jul 09 '23 at 11:14
You also can't aggregate inside a recursive CTE. Either way, without seeing sample data it's going to be hard to understand what's going on here, why is `supervisor` being joined with `job_title`? shouldn't it be joined with `employeed_id` or something like that? — Charlieface, Jul 09 '23 at 13:22

score 0 · Answer 1 · answered Jul 11 '23 at 10:30

It's not at all clear what you're trying to achieve here, as you've provided no example source data or expected output.

In a simple case, where you have a table of Employees with the Job Titles:

create table #employee_data
(
    id int,
    employee_name varchar(50),
    job_title varchar(50)
)

insert into #employee_data values
(1,'Emily Johnson','CEO'),
(2,'Benjamin Thompson','Software Engineer'),
(3,'Sophia Davis','Software Engineer'),
(4,'Daniel Mitchell','Software Engineer'),
(5,'Olivia Richardson','Marketing Manager'),
(6,'Liam Anderson','Customer Service Rep'),
(7,'Ava Edwards','Software Engineer'),
(8,'William Parker','Customer Service Rep'),
(9,'Mia Campbell','Software Engineer'),
(10,'James Adams','Software Engineer')

You can simply GROUP BY the job_title field and do a COUNT():

select
    job_title,
    count(*) as numEmployees
from #employee_data
group by job_title

Results:

/-------------------------------------\
|       job_title      | numEmployees |
|----------------------|--------------| 
|         CEO          |      1       |
| Customer Service Rep |      2       |
|  Marketing Manager   |      1       |
|  Software Engineer   |      6       |
\-------------------------------------/

If you're trying to do something more involved than this, then you need to better explain your database schema, provide some example data and expected output, as described here:

A question about the feasibility of a script

1 Answers1