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

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

Answer 2

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 3

Consider trying out a scalar-valued function (SQL Server 2000 or later).

CREATE FUNCTION dbo.GetOrdersSumByCurrency
(   
    @SalesPersonID INT, @CurrencyID INT 
)
RETURNS DECIMAL(10, 2) 
AS
BEGIN
    DECLARE @Sum DECIMAL(10, 2) 

    SELECT @Sum = ISNULL(SUM(SalesAmount), 0) FROM dbo.Orders 
    WHERE SalespersonID=@SalesPersonID AND CurrencyID = @CurrencyID

    RETURN @Sum

END

Then execute SQL such as this to get the results (assumes you also have a separate salespersons table, or otherwise use instead SELECT DISTINCT SalesPersonId.... FROM Orders) :

    SELECT SalesPersonId, 
dbo.GetOrdersSumByCurrency(SalesPersonId, 1) AS SumUSD, dbo.GetOrdersSumByCurrency(SalesPersonId, 2) AS SumCAD 
    FROM SalesPersons

Be sure to run query plans to see if it performs as you need compared against the other possibilities suggested here, especially if you are processing a large quantity of data.