Sum different row in column based on second column value

Question

I have an Orders table (simplified)

OrderId,
SalesPersonId,
SaleAmount,
CurrencyId,
...

I am attempting to create a report on this table, I'm hoping for something like:

SalesPersonId   TotalCAD    TotalUSD
1               12,345.00   6,789.00
2               7,890.00    1,234.00

I'd prefer not to do a self join (perhaps I'm optimizing prematurely, but this seems inefficient) IE:

SELECT SalesPersonId, SUM(OrdersCAD.SaleAmount), SUM(OrderUSD.SaleAmount)
FROM Orders
    LEFT JOIN Orders AS OrdersCAD ON Orders.OrderID AND Orders.CurrencyID = 1
    LEFT JOIN Orders AS OrdersUSD ON Orders.OrderID AND Orders.CurrencyID = 2

But I cannot think of another way to do this, any ideas?

Answer 1

Use a CASE block:

SELECT
  SalesPersonId,
  SUM(
    CASE CurrencyID
      WHEN 1 THEN SaleAmount
      ELSE 0
    END
  ) AS TotalCAD,
  SUM(
    CASE CurrencyID
      WHEN 2 THEN SaleAmount
      ELSE 0
    END
  ) AS TotalUSD
FROM Orders
GROUP BY SalesPersonId

Answer 2

Try This:

SELECT SalesPersonId, 
       SUM(CASE WHEN CurrencyID = 1 THEN SaleAmount ELSE 0 END) as CAD, 
       SUM(CASE WHEN CurrencyID = 2 THEN SaleAmount ELSE 0 END) as USD
FROM ORDERS