CODEWITHSUNDEEP
sqlsql-server
Nathan Koop
Nathan Koop

Reputation: 25197

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?

Upvotes: 2

Views: 6698

Answers (3)

dr.
dr.

Reputation: 1449

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

Upvotes: 2

PaulR
PaulR

Reputation: 356

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.

Upvotes: 0

Welbog
Welbog

Reputation: 60398

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

Upvotes: 8

Related Questions