sqlhive

cannot select the case when col to count


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


Solution

  • 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