Query help in SQL

Question

I have the below table, with empid, deptid, and Name. I need to get the results as empid,deptid,name and count of employees in each department.

CREATE TABLE emp(empid INTEGER PRIMARY KEY, deptid INTEGER, NAME TEXT);
/* Create few records in this table */
INSERT INTO emp VALUES
(1,100,'Tom'),
(2,200,'Lucy'),
(3,300,'Frank'),
(4,100,'Jane'),
(5,400,'Robert');

I need to get the results as empid,deptid,name, and count of employees in each department as below.

I am able to achieve results using the below queries.

SELECT a.empid, a.deptid, a.Name, result.emp_dept_count FROM emp a, 
    ( SELECT b.deptid, COUNT(b.deptid) AS emp_dept_count FROM emp b 
                  GROUP BY b.deptid ) result  
WHERE a.deptid = result.deptid;                 
/* using common table expression */
WITH emp_dept_count_cte(deptid,emp_dept_count) AS ( SELECT b.deptid, COUNT(b.deptid) AS emp_dept_count FROM emp b 
                  GROUP BY b.deptid )
SELECT a.empid, a.deptid, a.Name, result.emp_dept_count 
FROM emp a, (SELECT deptid, emp_dept_count FROM emp_dept_count_cte) result 
WHERE a.deptid = result.deptid;
/* using common table expression */
WITH emp_dept_count_cte (deptid,emp_dept_count) AS ( SELECT b.deptid, COUNT(b.deptid) AS emp_dept_count FROM emp b 
                  GROUP BY b.deptid )
SELECT a.empid, a.deptid, a.Name, emp_dept_count_cte.emp_dept_count 
FROM emp a
INNER JOIN emp_dept_count_cte
ON a.deptid = emp_dept_count_cte.deptid;
/* using common table expression */
WITH emp_dept_count_cte (deptid,emp_dept_count) AS ( SELECT b.deptid, COUNT(b.deptid) AS emp_dept_count FROM emp b 
                  GROUP BY b.deptid )
SELECT a.empid, a.deptid, a.Name, emp_dept_count_cte.emp_dept_count 
FROM emp a
LEFT JOIN emp_dept_count_cte
ON a.deptid = emp_dept_count_cte.deptid;

Is it possible to do this in alternate ways?

Answer 1

No need for CTE. Just do

SELECT *, COUNT(1) OVER (PARTITION BY deptid) AS emp_dept_count FROM emp