Got it, let's work through this. It looks like you've got four separate count queries against the employee_kras table, and you want to "associate" their results—most likely either combine them into a single row with each count as a distinct column, or use this aggregated data in a join with another table. Here are two practical, efficient approaches:

Option 1: Combine Counts Using Subquery Cross Joins #

If you want to keep each count as a separate subquery and join their results (great if you need to later extend this to join with other tables), use CROSS JOIN since each subquery returns a single row/value:

SELECT
  q1.count_null_appraisal_type,
  q2.count_approved_team_kra_null_appraisal,
  q3.count_not_submitted_with_appraisal_type,
  q4.count_submitted_no_mgr_rating
FROM
  (SELECT count(staff_id) AS count_null_appraisal_type FROM employee_kras WHERE appraisal_type_id IS NULL) q1
CROSS JOIN
  (SELECT count(staff_id) AS count_approved_team_kra_null_appraisal FROM employee_kras WHERE Team_KRA_Status='approved' AND Appraisal_status IS NULL) q2
CROSS JOIN
  (SELECT count(staff_id) AS count_not_submitted_with_appraisal_type FROM employee_kras WHERE appraisal_status='not submitted' AND Appraisal_Type_ID IS NOT NULL) q3
CROSS JOIN
  (SELECT count(staff_id) AS count_submitted_no_mgr_rating FROM employee_kras WHERE Appraisal_status='submitted' AND Mgr_Rating_id IS NULL) q4;

This will return one row where each column holds the count from your original queries. If you need to join this with another table later, just wrap this entire block in a subquery and add your join condition.

Option 2: Single Query with CASE WHEN (More Efficient) #

Instead of scanning the table four times (once per query), you can calculate all counts in a single pass using CASE WHEN clauses. This is way more performant, especially for large tables:

SELECT
  COUNT(CASE WHEN appraisal_type_id IS NULL THEN staff_id END) AS count_null_appraisal_type,
  COUNT(CASE WHEN Team_KRA_Status='approved' AND Appraisal_status IS NULL THEN staff_id END) AS count_approved_team_kra_null_appraisal,
  COUNT(CASE WHEN appraisal_status='not submitted' AND Appraisal_Type_ID IS NOT NULL THEN staff_id END) AS count_not_submitted_with_appraisal_type,
  COUNT(CASE WHEN Appraisal_status='submitted' AND Mgr_Rating_id IS NULL THEN staff_id END) AS count_submitted_no_mgr_rating
FROM employee_kras;

How this works: CASE WHEN returns staff_id only if the condition is met, otherwise it returns NULL. COUNT() ignores NULL values, so it only counts rows that match each specific condition.

If You Need to Join with Another Table #

If your "association" means joining these counts to another table (like a department or team table), you can extend the second approach with a GROUP BY clause to aggregate by a shared key, then join:

SELECT
  d.department_name,
  stats.count_null_appraisal_type,
  stats.count_approved_team_kra_null_appraisal
FROM departments d
JOIN (
  SELECT
    department_id, -- Assuming `employee_kras` has a department_id field linking to `departments`
    COUNT(CASE WHEN appraisal_type_id IS NULL THEN staff_id END) AS count_null_appraisal_type,
    COUNT(CASE WHEN Team_KRA_Status='approved' AND Appraisal_status IS NULL THEN staff_id END) AS count_approved_team_kra_null_appraisal,
    COUNT(CASE WHEN appraisal_status='not submitted' AND Appraisal_Type_ID IS NOT NULL THEN staff_id END) AS count_not_submitted_with_appraisal_type,
    COUNT(CASE WHEN Appraisal_status='submitted' AND Mgr_Rating_id IS NULL THEN staff_id END) AS count_submitted_no_mgr_rating
  FROM employee_kras
  GROUP BY department_id
) stats ON d.department_id = stats.department_id;

This will give you counts broken down by department, joined with the department names from your departments table.

内容的提问来源于stack exchange，提问作者poonam