CODEWITHSUNDEEP
sqlsubquery
Joseph.Scott.Garza
Joseph.Scott.Garza

Reputation: 133

Sub query calculation

How can I get my subquery to calculate each employees On-Time rate, group them, and then sort them in Desc order?

SELECT 
    EMPLOYEE_NAME AS EMPLOYEE, 
    (
        SELECT 
        (
            SELECT 
                CAST(COUNT(APE_BUSDRIVER_STATUS_OBJID) AS DECIMAL) 
            FROM dbo.APE_BUSDRIVER_MAIN 
            WHERE APE_AREA_OBJID = 3 
                  AND YEAR_TIME = '2014' 
                  AND ACTIVE = 1 
                  AND APE_BUSDRIVER_STATUS_OBJID = 1
        )
        /
        ( 
            SELECT 
                CAST(COUNT(APE_BUSDRIVER_STATUS_OBJID) AS DECIMAL) 
            FROM dbo.APE_BUSDRIVER_MAIN 
            WHERE APE_AREA_OBJID = 3 
                  AND YEAR_TIME = '2014' 
                  AND     ACTIVE     = 1
        )
    ) AS YIELD
FROM dbo.APE_BUSDRIVER_MAIN 
WHERE YEAR_TIME = '2014' 
      AND APE_AREA_OBJID = 3 
      AND ACTIVE = 1 
GROUP BY EMPLOYEE_NAME 
ORDER BY YIELD DESC

RESULTS:
enter image description here

as you can see, the sub query is giving the same results for each employee.

here is the table FYI: enter image description here

continued...

enter image description here

NOTE:
APE_BUSDRIVER_STATUS_OBJID:
1 = ON-TIME
2 = LATE

Upvotes: 0

Views: 101

Answers (2)

user2839610
user2839610

Reputation: 

SELECT 
    EMPLOYEE_NAME AS EMPLOYEE,
    CAST(COUNT(CASE WHEN APE_BUSDRIVER_STATUS_OBJID = 1 THEN APE_BUSDRIVER_STATUS_OBJID END) AS DECIMAL) /
        CAST(COUNT(APE_BUSDRIVER_STATUS_OBJID) AS DECIMAL) AS YIELD
FROM
    dbo.APE_BUSDRIVER_MAIN 
WHERE
    YEAR_TIME = '2014' AND
    APE_AREA_OBJID = 3 AND
    ACTIVE = 1 
GROUP BY
    EMPLOYEE_NAME 
ORDER BY
    YIELD DESC

Upvotes: 1

Jeff
Jeff

Reputation: 1

You don't want to use a subquery at all; the subquery bypasses the where clause and gives you the same result for each employee. I think this is close to giving you what you want:

SELECT 
  EMPLOYEE_NAME,
  CAST(COUNT(APE_BUSDRIVER_STATUS_OBJID) AS DECIMAL) 
  /
  CAST(COUNT(APE_BUSDRIVER_STATUS_OBJID) AS DECIMAL) 
  AS YIELD,
FROM 
  dbo.APE_BUSDRIVER_MAIN 
  WHERE 
    YEAR_TIME = '2014' 
    AND APE_AREA_OBJID = 3 
    AND ACTIVE = 1 
  GROUP BY EMPLOYEE_NAME 
  ORDER BY YIELD 
    DESC

Upvotes: 0

Related Questions