CODEWITHSUNDEEP
sqlgroup-by
rcardinaux
rcardinaux

Reputation: 231

SQL - conditional "group by"

I have a table with clients, amount on their account and the corresponding currency. I need to select the 100 clients with the highest amount on their account considering only 2 currencies (USD and EUR) --> all the amounts are already converted in USD. Here is an extract of my table:

client |  amount | currency
1      |  100    |  USD
2      |  20     |  HKD
3      |  50     |  EUR
1      |  15     |  EUR

I am only interest in the USD and EUR, I have to preserve the amount in each currency, and rank the clients according to the total amount in those two currencies. The desired output for the table above would be the following:

client |  amount in USD |  amount in EUR | total value
1      |  100           |  15            | 115
3      |  0             |  50            | 50

Any help would be greatly appreciated! Cheers, Rapha

Upvotes: 1

Views: 5369

Answers (3)

dhvarela
dhvarela

Reputation: 36

Assuming that your table name is 'clients':

    SELECT 
    client, 
    SUM(CASE c.currency WHEN 'USD' THEN c.amount ELSE 0 END) AS amount_in_usd,
    SUM(CASE c.currency WHEN 'EUR' THEN c.amount ELSE 0 END) AS amount_in_eur,
    SUM(amount) AS total_value
    FROM clients AS c
    WHERE c.currency = 'USD' OR c.currency='EUR'
    GROUP BY c.client
    ORDER BY total_value DESC

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

using conditional aggregation (e.g. in Sql Server):

select 
    client
  , sum(case when currency = 'USD' then amount else 0 end) as AmountInUSD
  , sum(case when currency = 'EUR' then amount else 0 end) as AmountInEUR
  , sum(case when currency in ('USD','EUR') then amount else 0 end) as TotalValue
from t
group by client
having sum(case when currency in ('USD','EUR') then amount else 0 end) > 0
order by TotalValue desc

rextester demo (for sql server): http://rextester.com/LJF89562

returns:

+--------+-------------+-------------+------------+
| client | AmountInUSD | AmountInEUR | TotalValue |
+--------+-------------+-------------+------------+
|      1 |         100 |          15 |        115 |
|      3 |           0 |          50 |         50 |
+--------+-------------+-------------+------------+

Upvotes: 2

Rahul
Rahul

Reputation: 77866

You can use CASE expression with group by client like 

sum(case when currency = 'USD' then amount) as amount in USD

Upvotes: 0

Related Questions