I have the following query why I"m attempting to simulate a COUNTIF across two tables:

WITH Range__A1_A4 (val) AS (
    SELECT 1 UNION ALL 
    SELECT 2 UNION ALL 
    SELECT 8 UNION ALL 
    SELECT 10
), NFL (SEASON, TEAM, WINS) AS (
    SELECT 2010, 'KC', 10 UNION ALL
    SELECT 2011, 'GB', 11
)
SELECT 
    (SELECT SUM(CASE WHEN NFL.WINS>R.val THEN 1 ELSE 0 END) FROM Range__A1_A4 R) ct,
    *
FROM NFL;

From this I get the following error:

Unsupported subquery type cannot be evaluated

What's wrong with this query and what would be the proper way to fix it?

CodePudding user response：

I would write it like this:

WITH Range__A1_A4 (val) AS (
    SELECT * FROM VALUES 
        (1),
        (2),
        (8),
        (10)
), NFL (SEASON, TEAM, WINS) AS (
    SELECT * FROM VALUES 
        (2010, 'KC', 10),
        (2011, 'GB', 11)
)
SELECT 
    n.*,
    SUM(iff( n.wins > r.val, 1, 0)) as ct_a,
    count_if(n.wins > r.val) as ct_b
FROM NFL AS n
JOIN Range__A1_A4 AS r
group by 1,2,3;