Leecode-SQL 1393. Capital Gains and Losses on Stocks

Two solutions, the first is subtle
An if solution
sum is the sum in the field, and the if tag is used to judge, if it is a buy, it will automatically become a negative sum, and if it is sold, it will be automatically added

select stock_name,sum(if(operation='Buy',-price,price))span> as capital_gain_lossfrom Stocksgroup by stock_name

The other is case

case functionThe syntax of MySQL's case function is:1.Simple functionCASE [column_name] WHEN [value1] THEN[result1]... ELSE [default] ENDBe careful when querying multiple tables.CASE hero_nameWHEN 'Galen' THEN 'Top order'WHEN 'Lux' THEN 'Mid single'WHEN 'Jinx' THEN 'ADC'WHEN 'Piano Girl' THEN 'Auxiliary'WHEN 'Blind monk' THEN 'Jungle'ELSE 'Bitch' END

The effect is similar to if

select stock_name,sum(casewhen operation='buy' then -priceelse price end) as 'capital_gain_loss'from Stocksgroup by stock_name