Nathan Koop Nathan Koop - 23 days ago 9
SQL Question

Sum different row in column based on second column value

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 Source

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