SQL: Scalar Subquery with Case When... Else...

Lets first create a table:

Create database blog_port;

use blog_portal;

-- Create the Employee table

CREATE TABLE Employee (

employee_id INT,

team_id INT,

PRIMARY KEY (employee_id)

);

-- Insert the data into the Employee table

INSERT INTO Employee (employee_id, team_id)

VALUES (1, 8),

(2, 8),

(3, 8),

(4, 7),

(5, 9),

(6, 9);

Write an SQL query to find the team size of each of the employees. Return result table in any order. The query result format is in the following example.

First intuitive way to solve the problem:

with CTE as (Select

team_id, count(team_id) as team_mem_count

from Employee

group by team_id)

select e.employee_id, CTE.team_mem_count

from Employee as e

Right join CTE

on CTE.team_id = e.team_id

order by e.employee_id

;

Best Way: Case When Scalar Grouping

SELECT employee_id,

team_id,

CASE

WHEN team_id IS NULL THEN 0

ELSE (SELECT COUNT(*) FROM Employee WHERE team_id = e.team_id)

END AS team_size

FROM Employee AS e;

In this case, the subquery acts as a scalar subquery,

which can be used in the SELECT statement without requiring a GROUP BY clause.

The subquery is evaluated independently for each row,

providing the corresponding team_size for each employee without the need for explicit grouping.

For example, when the subquery condition of the 'case when' runs - WHERE team_id = e.team_id it will only match the team_id of the current row to the team ids of the employee as e table. Therefore, keep in mind, each row will return the count(*) for only the matching rows values to the condition.