Top Earners

  • + 0 comments

    MS SQL SERVER

    WITH 
    TOTAL_E AS 
    (
        SELECT
            EMPLOYEE_ID,
            SALARY * MONTHS AS TOTAL_SALARY
        FROM EMPLOYEE
    ),
    MAX_TOTAL AS 
    (
        SELECT MAX(TOTAL_SALARY) AS MAX_SALARY
        FROM TOTAL_E
    ),
    NUM_SAL_MAX AS 
    (
        SELECT COUNT(*) AS NUM_EMPLOYEES
        FROM TOTAL_E
        WHERE TOTAL_SALARY = (SELECT MAX_SALARY FROM MAX_TOTAL)
    )
    SELECT 
        CAST(MAX_TOTAL.MAX_SALARY AS VARCHAR) + '  ' + CAST(NUM_SAL_MAX.NUM_EMPLOYEES AS VARCHAR) AS RESULT
    FROM 
        MAX_TOTAL, NUM_SAL_MAX;