SELECT SUM and remove similar rows

Question

SELECT 
SUM(coins.available_supply * hist_all.price) AS market_cap_usd_all 
FROM coins
    LEFT JOIN hist_all ON coins.id = hist_all.coins_id
    WHERE hist_all.price != 0
AND (hist_all.second_coin = 'USD' or hist_all.second_coin = 'USDT');

I try get sum market_cap_usd_all, but in my table are rows with USD and USDT:

My query should like this:

if exists row second_coin = USD, DON'T GET this same row with 'USDT', but if don't exists row with second_coin = USD check if it exists row with second_coin = USDT and get him. So the table should like this:

I don't know how write query with WHERE IF I tried this:

... WHERE IF (hist_all.second_coin = 'USD', hist_all.second_coin = 'USD', hist_all.second_coin = 'USDT')

but it still sums all rows, both from USD and USDT.

Answer 1

You need a join or a correlated subquery (with `EXISTS), in order to compare rows:

SELECT 
    SUM(c.available_supply * h.price) AS market_cap_usd_all 
FROM coins AS c
    JOIN hist_all AS h
    ON c.id = h.coins_id
WHERE h.price <> 0
  AND ( h.second_coin = 'USD' 
     OR 
        h.second_coin = 'USDT'
        AND NOT EXISTS
            ( SELECT 1
              FROM hist_all AS hi
              WHERE hi.first_coin = h.first_coin
                AND hi.second_coin = 'USD'
            )
      ) ;