0
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:

enter image description here

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:

enter image description here

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.

  • 2
    Trying to understand here. Are you just trying to get a sum of market_cap_usd_all for anything that is USD only? Or are you trying to get the total of all USD and USDT? – Shooter McGavin May 3 '18 at 17:37
  • @ShooterMcGavin, no, I try get market_cap_usd_all from USD and USDT, BUT IF EXIST USD, CAN NOT DOWNLOAD VALUE WITH USDT. For example I have 2 same records: first_coin = BTC with USD and USDT, in this example I need get only value from market_cap_usd_all where second_coin = USD. – michal May 3 '18 at 18:05
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'
            )
      ) ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.