Improve Query to not have to run multiple times?-CodePudding

I currently have this working query. However, I have to run it multiple times with different parameters to get the total result. I'm wondering if there's a way I can run a loop or create a column for each query within one query so I can get the complete result without commenting in specific params and then commenting out (Running it multiple times). Is there a way to create a column for ether, BTC, tether, and usd coin?

SELECT DISTINCT
  contact.account_id,
  contact.email,
  contact.tax_country,
  crypto.total_crypto
FROM
  (
    SELECT DISTINCT
      account_id,
      SUM(ABS(unit_count)) as total_crypto
    FROM
      beta.asset_transaction
    GROUP BY
      account_id, comments_1
    HAVING
    -- ((comments_1 = 'Sell Bitcoin' OR comments_1 = 'Buy Bitcoin') AND SUM(ABS(unit_count)) > '0.025')
    ((comments_1 = 'Sell Ether' OR comments_1 = 'Buy Ether') AND SUM(ABS(unit_count)) > '0.4')
    -- ((comments_1 = 'Sell Tether USD' OR comments_1 = 'Buy Tether USD') AND SUM(ABS(unit_count)) > '0.4')
      -- ((comments_1 = 'Sell USD Coin' OR comments_1 = 'Buy USD Coin') AND SUM(ABS(unit_count)) > '0.4')--   (asset_transfer_type = 'Sell Tether USD' AND SUM(ABS(unit_count)) > '1000')
  ) AS crypto
  INNER JOIN contact ON contact.account_id = crypto.account_id
  ORDER BY total_crypto DESC;

CodePudding user response：

As Nick mentioned in the comments , the "group by" has "comments_1" but it is not there in column projection which needs fixed. "Filter" clause can be used to create a column for each type of metrics. Here is the SQL which needs validated(could not validate as there is no sample data). From the output of this SQL , the column of interest can be visually chosen.

with cte_unit_count
as
(
 select SUM(ABS(unit_count)) cnt from  beta.asset_transaction
)
SELECT DISTINCT
  contact.account_id,
  contact.email,
  contact.tax_country,
  crypto.crypto_bitcoin,
  crypto.crypto_ether,
  crypto.crypto_tether,
  crypto.usd_coin,
  crypto.usd_tether
FROM
  (
    SELECT DISTINCT
      account_id,
      SUM(ABS(unit_count)) filter (where  (comments_1 = 'Sell Bitcoin' OR comments_1 = 'Buy Bitcoin') and  cte_unit_count.cnt > '0.025' ) crypto_bitcoin,
      SUM(ABS(unit_count)) filter (where (comments_1 = 'Sell Ether' OR comments_1 = 'Buy Ether') and  cte_unit_count.cnt > '0.4' ) crypto_ether,
      SUM(ABS(unit_count)) filter (where(comments_1 = 'Sell Tether USD' OR comments_1 = 'Buy Tether USD') and  cte_unit_count.cnt > '0.4' ) crypto_tether,
      SUM(ABS(unit_count)) filter (where(comments_1 = 'Sell USD Coin' OR comments_1 = 'Buy USD Coin') AND cte_unit_count.cnt > '0.4') usd_coin ,
      SUM(ABS(unit_count)) filter (where asset_transfer_type = 'Sell Tether USD' AND cte_unit_count.cnt > '1000') ) usd_tether
    FROM
      beta.asset_transaction , cte_unit_count
    GROUP BY
      account_id, <...verify --->comments_1>
  ) AS crypto
  INNER JOIN contact ON contact.account_id = crypto.account_id
  order by <...whichever column you desire...>