Question 58183 in Mysql Question posted in
The official MySQL documentation can be found here.

How to update the column value in sql based on other column value – Mysql

I have data like below

Amount currency Investors
250 USD Coinvest I
0 GBP Coinvest I
860 USD Paralell
0 GBP Paralell
121 USD PSLO
5 NULL PSLO

I need output like below

Amount currency Investors
250 GBP Coinvest I
860 GBP Paralell
126 USD PSLO

i.e scenario – I want to club/group by Investors and aggregate amount and currency field should be when its USD or Null it should be USD or when its GBP or USD it should display GBP.

Query is not working:

select sum(Amount)  
case when currency in ('USD' , NULL ) then 'USD'
else currency end as c1, Investors
from table 
group by c1 , Investors;

Tried:

select sum(Amount)  
case when currency in ('USD' , NULL ) then 'USD'
else currency end as c1, Investors
from table 
group by c1 , Investors;
Tags:

2

Answers


  1. 0

    For this exact case you can use sum fro amount, and min fro currency.

    select sum(Amount) amount,
    min(currency),
    Investors
from table 
group by Investors

    Demo here.

    Login or Signup to reply.
  2. 0

    You could use row_number() for prioritization:

    select amount, currency, investors
from (
    select t.*,
        row_number() over(
            partition by investors
            order by case currency
                when 'GBP' then 1
                when 'USD' then 2
                else 3
            end
        ) rn
    from mytable
) t
where rn = 1

    When an investor has more than one row, this puts first the row with ‘GBP’, then ‘USD’, and then other values (here, null). You can easily adapt the case expression to take in account more currencies or a different prioritization sequence.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search