Subquery must have alias despite having alias?

I have an Alias at the bottom (AS crypto). However, I still get the “subquery in FROM must have an alias” error. And then HINT “For example, FROM (SELECT …) [AS] foo.” I see many other posts that say to “just add AS _” as an alias, but mine still isn’t working. What am I doing wrong?

  SELECT DISTINCT
  contact.account_id,
  contact.email,
  crypto.total_crypto
FROM
  (
    SELECT DISTINCT
      account_id,
      SUM(unit_count) as total_crypto
    FROM
      beta.asset_transaction
    WHERE
      (asset_transfer_type = 'bitcoin' AND SUM(unit_count) > '0.025' AND account_id != '22222')
    OR
      (asset_transfer_type = 'ethereum' AND SUM(unit_count) > '0.4' AND account_id != '22222')
    OR
      (asset_transfer_type = 'internal' AND SUM(unit_count) > '1000' AND account_id != '22222')
    OR 
      asset_transfer_type = 'stellar' AND SUM(unit_count) > '5000' AND account_id != '22222')
    GROUP BY
      account_id
  ) AS crypto ---> Alias
  INNER JOIN beta.contact ON contact.account_id = crypto.account_id
  ORDER BY total_crypto DESC;

Answer

Remember:

1. We can’t put an aggregate in a where clause

2. We can’t use DISTINCT with aggregates

I believe what you want is “By account ID, the total unit count summed across the four listed asset transfer types for any account ID that has a unit count of more than 0.025 bitcoin, or more than 0.4 ethereum, or more than 1000 internal or more than 5000 stellar”.

In which case:

  SELECT DISTINCT
         contact.account_id,
         contact.email,
         crypto.total_crypto
  FROM
   (
    SELECT 
      account_id,
      asset_transfer_type,
      SUM(unit_count) AS asset_transfer_unit_count
      SUM(SUM(unit_count)) OVER (PARTITION BY account_id) as total_crypto
    FROM
      beta.asset_transaction
    WHERE account_id != '22222'
    AND asset_transfer_type IN ('bitcoin','ethereum','internal','stellar')
    GROUP BY account_id, asset_transfer_type
    ) crypto
  INNER JOIN beta.contact ON contact.account_id = crypto.account_id
    WHERE
      (asset_transfer_type = 'bitcoin' AND asset_transfer_unit_count > 0.025)
    OR
      (asset_transfer_type = 'ethereum' AND asset_transfer_unit_count > 0.4)
    OR
      (asset_transfer_type = 'internal' AND asset_transfer_unit_count > 1000)
    OR 
      (asset_transfer_type = 'stellar' AND asset_transfer_unit_count > 5000)
  ORDER BY total_crypto DESC;