Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

In 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

I have the above query... it can't seems to be able to run.

Basically, I have this table BuyProductDetails which stores all the orders from different Sellers.

Some orders will have TransactionFee.

Now, what I need is to calculate the total sales of these orders with TransactionFee, and the total sales for these sellers including those orders without TransactionFee.

The result set should have the following fields:

  1. SellerID
  2. Sum of Transaction fee
  3. Sum of total sales
  4. Number of Orders with Transaction fee
  5. Qty ordered
  6. Total sales for that seller

But when I run this sql, it returns the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any help is much appreciated. Thank you.

share|improve this question
1  
I got the solution. It was from an answer by someone but I can't see his answer now on this page. Wonder why? Basically, just amend the subquery to the following: SELECT SUM(b.Quantity*b.PriceItem) as WholeMonthTransactionValue from BuyProductDetails b where b.SellerID = a.SellerID – J K Mar 14 at 7:07

2 Answers

up vote 0 down vote

You can use more effective option with CASE expression

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

Demo on SQLFiddle

Or merely add correct alias in the subquery

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

Demo on SQLFiddle

share|improve this answer
up vote 0 down vote

Tried something like this ?

 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)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.