Additionner les différentes lignes de la colonne en fonction de la valeur de la deuxième colonne.

Question

J'ai un tableau de commandes (simplifié)

OrderId,
SalesPersonId,
SaleAmount,
CurrencyId,
...

J'essaie de créer un rapport sur ce tableau, j'espère quelque chose comme.. :

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

Je préférerais ne pas faire d'auto-jonction (peut-être que j'optimise prématurément, mais cela semble inefficace) 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

Mais je n'arrive pas à trouver une autre façon de procéder, des idées ?

Answer 1

Utilisez un bloc CASE :

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

Essayez ça :

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 3

Pensez à essayer une fonction à valeur scalaire (SQL Server 2000 ou ultérieur).

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

Exécutez ensuite un SQL tel que celui-ci pour obtenir les résultats (en supposant que vous ayez également une table séparée pour les vendeurs, ou sinon utilisez plutôt SELECT DISTINCT SalesPersonId.... FROM Commandes) :

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

Veillez à exécuter des plans de requête pour voir si elle donne les résultats escomptés par rapport aux autres possibilités suggérées ici, surtout si vous traitez une grande quantité de données.