This is the SQL query I wrote:
SELECT
COUNT(*) AS TOTLA_COUNT,
COUNT(
CASE
WHEN ( market_val > 0 AND ABS( loan_amt / market_cal * 100 - ratio ) > 5 ) THEN 1
ELSE NULL
END
) AS FAIL_COUNT,
COUNT( FAIL_COUNT / TOTAL_COUNT * 100 ) AS FAIL_RATE
FROM
MY_TABLE
but I get an error
Reason for Error is cannot resolve 'market_val' given input columns
May I know how I can fix it? Thanks
Other Way.
AsIs:
SELECT
COUNT(*) AS TOTLA_COUNT,
COUNT(
CASE
WHEN ( market_val > 0 AND ABS( loan_amt / market_cal * 100 - ratio ) > 5 ) THEN 1
ELSE NULL
END
) AS FAIL_COUNT,
COUNT( FAIL_COUNT / TOTAL_COUNT * 100 ) AS FAIL_RATE
FROM
MY_TABLE
ToBe:
SELECT a.TOTLA_COUNT, a.FAIL_COUNT
, FAIL_COUNT / TOTAL_COUNT * 100 AS FAIL_RATE
FROM (
SELECT
COUNT(*) AS TOTLA_COUNT,
COUNT(
CASE
WHEN ( market_val > 0 AND ABS( loan_amt / market_cal * 100 - ratio ) > 5 ) THEN 1
ELSE NULL
END
) AS FAIL_COUNT
FROM
MY_TABLE
) a