Unable to use single-valued column from subquery table join in HAVING clause with aggregate value

Question

Related question upon which I based this question here

For relevant tables and columns (a lot more exist than the following), I have a customer table with cust_id and state columns and an account table with account_id, cust_id, and avail_balance columns.

Example customer table:

| cust_id | state |
|--------:|-------|
|       1 | MA    |
|       2 | MA    |
|       3 | MA    |
|       4 | MA    |
|       5 | NH    |
|       6 | MA    |
|       7 | MA    |
|       8 | NH    |
|       9 | MA    |
|      10 | NH    |
|      11 | MA    |
|      12 | NH    |
|      13 | MA    |

Example account table:

| account_id | cust_id | avail_balance |
|-----------:|--------:|--------------:|
|          1 |       1 |       1057.75 |
|          2 |       1 |           500 |
|          3 |       1 |          3000 |
|          4 |       2 |       2258.02 |
|          5 |       2 |           200 |
|          7 |       3 |       1057.75 |
|          8 |       3 |        2212.5 |
|         10 |       4 |        534.12 |
|         11 |       4 |        767.77 |
|         12 |       4 |       5487.09 |
|         13 |       5 |       2237.97 |
|         14 |       6 |        122.37 |
|         15 |       6 |         10000 |
|         17 |       7 |          5000 |
|         18 |       8 |       3487.19 |
|         19 |       8 |        387.99 |
|         21 |       9 |        125.67 |
|         22 |       9 |       9345.55 |
|         23 |       9 |          1500 |
|         24 |      10 |      23575.12 |
|         25 |      10 |             0 |
|         27 |      11 |       9345.55 |
|         28 |      12 |      38552.05 |
|         29 |      13 |         50000 |

Here is the format of the query which I wish to execute.

SELECT a.cust_id, SUM(a.avail_balance) AS cust_tot_bal
FROM
    account AS a
    INNER JOIN customer c ON a.cust_id = c.cust_id
    CROSS JOIN (SELECT MAX(cust_id) AS max_nh_cust_id, MAX(nh_cust_tot_bal) AS max_nh_cust_tot_bal
                FROM
                    (SELECT a.cust_id, SUM(avail_balance) AS nh_cust_tot_bal
                     FROM
                         account AS a
                         INNER JOIN customer c ON a.cust_id = c.cust_id
                     WHERE
                         c.state = 'NH'
                     GROUP BY a.cust_id) AS nh_cust_tot_bal) AS max_nh_cust_tot_bal_t
WHERE c.state = 'MA'
AND a.cust_id > max_nh_cust_id
GROUP BY a.cust_id;

This fails since it is unable to detect max_nh_cust_tot_bal from the previous join.

Expected result for the above example data (the cust_tot_bal column is optional):

| cust_id | cust_tot_bal |
|--------:|-------------:|
|      13 |        50000 |

Answer 1

You can get the maximum cust_id and sum of the customers of state 'NH' with this query:

SELECT MAX(cust_id) max_nh_cust_id, 
       MAX(t.max_nh_avail_balance) max_nh_avail_balance
FROM (
  SELECT a.cust_id, SUM(a.avail_balance) AS max_nh_avail_balance
  FROM account a INNER JOIN customer c 
  ON a.cust_id = c.cust_id
  WHERE c.state = 'NH'
  GROUP BY a.cust_id
) t

and you can join it like this:

SELECT m.cust_id, m.cust_tot_bal
FROM (
  SELECT a.cust_id, SUM(a.avail_balance) AS cust_tot_bal
  FROM account a INNER JOIN customer c 
  ON a.cust_id = c.cust_id
  WHERE c.state = 'MA'
  GROUP BY a.cust_id
) m  
INNER JOIN (
  SELECT MAX(cust_id) max_nh_cust_id, 
         MAX(t.max_nh_avail_balance) max_nh_avail_balance
  FROM (
    SELECT a.cust_id, SUM(a.avail_balance) AS max_nh_avail_balance
    FROM account a INNER JOIN customer c 
    ON a.cust_id = c.cust_id
    WHERE c.state = 'NH'
    GROUP BY a.cust_id
  ) t 
) n ON m.cust_id > n.max_nh_cust_id AND m.cust_tot_bal > n.max_nh_avail_balance

See the demo.
Results

> cust_id | cust_tot_bal
> ------: | -----------:
>      13 |        50000