Nested counts from multiple columns-CodePudding

I have the following 2 tables.

Employee

EMPID License  Experience  Salary
----  ------  ----------  ------
ABC1  3256     5 years     $1000
ABC2  1324     10 years    $3000
ABC3  2345     11 years    $2500

Machine_Control

MID   MCODE OPRID   SMID   SMCODE  MLOCATION
-------------------------------------------
M1     1     ABC1      NULL    NULL   LOCATION1
M1     2     ABC2      NULL    NULL   LOCATION2
M1     3     NULL      M1      1      LOCATION1
M1     4     ABC1      NULL    NULL   LOCATION3
M1     5     NULL      M1      2      LOCATION2

Looking for the following output. Note that both primary (MID,MCODE) and secondary (SMID, SMCODE) should be counted for no_machines and No_locations for a given EMPID. MID and MCODE are primary key of the table.

EMPID  License Experience No_Machines  No_Locations
--------------------------------------------------
ABC1    3256      5 years     3              2
ABC2    1324      10 years    2              1
ABC3    2345      11 years    0              0

I tried the following

select a.EMPID, a.Licesne, a.Experience, count(b.MID) No_Machines, count(distinct b.MLOCATION) No_Locations
from Employee a
    left join Machine_Control b on a.EMPID= b.OPRDID
group by a.EMPID;

I'm getting the following results

EMPID  License Experience No_Machines  No_Locations
--------------------------------------------------
ABC1    3256      5 years     2              2
ABC2    1324      10 years    1              1
ABC3    2345      11 years    0              0

CodePudding user response：

Combine the results of getting SMID and SMCODE with UNION ALL.

SELECT
  a.EMPID, a.License, a.Experience,
  COALESCE(b.No_Machines, 0) No_Machines,
  COALESCE(b.No_Locations, 0) No_Locations
FROM Employee a
  LEFT JOIN (
    SELECT OPRID,
      COUNT(MID) No_Machines,
      COUNT(distinct MLOCATION) No_Locations
    FROM (
      SELECT MID, MCODE, OPRID, MLOCATION
        FROM Machine_Control
      UNION ALL
      SELECT s.SMID, s.SMCODE, p.OPRID, p.MLOCATION
        FROM Machine_Control s JOIN Machine_Control p
        ON s.SMID=p.MID AND s.SMCODE=p.MCODE
      ) ps
    GROUP BY OPRID) b
  ON a.EMPID= b.OPRID

DB Fiddle