Dans MS Sql.
SELECT a.SellerID, SUM(TransactionFee) as TransactionFees, SUM(Quantity*a.PriceItem) as TransactionValue, COUNT(*) as OrdersWithTransactionFees, SUM(Quantity) as Qty, (SELECT SUM(a.Quantity*a.PriceItem) as WholeMonthTransactionValue from BuyProductDetails where SellerID = a.SellerID) as aa FROM BuyProductDetails as a WHERE MONTH(a.OrderDate)=3 AND YEAR(a.OrderDate)=2013 AND TransactionFee IS NOT NULL GROUP BY a.SellerID
J'ai la requête ci-dessus … il ne semble pas pouvoir fonctionner.
Fondamentalement, j'ai cette table BuyProductDetails qui stocke toutes les commands de différents vendeurs.
Certaines commands auront TransactionFee.
Maintenant, ce dont j'ai besoin est de calculer le total des ventes de ces commands avec TransactionFee, et le total des ventes pour ces vendeurs, y compris ceux sans TransactionFee.
Le jeu de résultats doit avoir les champs suivants:
Mais quand je lance ce sql, il renvoie l'erreur suivante:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Toute aide est très appréciée. Je vous remercie.
J'ai essayé quelque chose comme ça?
SELECT a.SellerID, SUM(TransactionFee) as TransactionFees, SUM(Quantity*a.PriceItem) as TransactionValue, COUNT(*) as OrdersWithTransactionFees, SUM(Quantity) as Qty, MIN(a.WholeMonthTransactionValue) as WholeMonthTransactionValue FROM BuyProductDetails as a, (SELECT b.SellerID, SUM(b.Quantity*b.PriceItem) as WholeMonthTransactionValue, MONTH(b.OrderDate), YEAR(b.OrderDate) FROM BuyProductDetails b GROUP BY b.SellerID, MONTH(b.OrderDate) as MonthID, YEAR(b.OrderDate) as YearID) as aa WHERE MONTH(a.OrderDate)=3 AND YEAR(a.OrderDate)=2013 AND TransactionFee IS NOT NULL AND a.SellerID = aa.SellerID AND MONTH(a.OrderDate)=aa.MonthID AND YEAR(a.OrderDate) = aa.YearID GROUP BY a.SellerID)
Vous pouvez utiliser une option plus efficace avec l'expression CASE
SELECT a.SellerID, SUM(CASE WHEN TransactionFee IS NOT NULL THEN TransactionFee END) AS TransactionFees, SUM(CASE WHEN TransactionFee IS NOT NULL THEN Quantity * PriceItem END) AS TransactionValue, COUNT(CASE WHEN TransactionFee IS NOT NULL THEN 1 END) as OrdersWithTransactionFees, SUM(CASE WHEN TransactionFee IS NOT NULL THEN Quantity END) as Qty, SUM(Quantity * PriceItem) AS WholeMonthTransactionValue FROM BuyProductDetails AS a WHERE MONTH(a.OrderDate) = 3 AND YEAR(a.OrderDate) = 2013 GROUP BY a.SellerID
Démo sur SQLFiddle
Ou ajoutez simplement un alias correct dans la sous-requête
SELECT a.SellerID, SUM(TransactionFee) as TransactionFees, SUM(Quantity*a.PriceItem) as TransactionValue, COUNT(*) as OrdersWithTransactionFees, SUM(Quantity) as Qty, (SELECT SUM(d.Quantity * d.PriceItem) FROM BuyProductDetails d WHERE d.SellerID = a.SellerID) as WholeMonthTransactionValue FROM BuyProductDetails as a WHERE MONTH(a.OrderDate)=3 AND YEAR(a.OrderDate)=2013 AND TransactionFee IS NOT NULL GROUP BY a.SellerID
Démo sur SQLFiddle