CODEWITHSUNDEEP
sqlsql-server
Danny Ryu
Danny Ryu

Reputation: 39

Subquery returned more than 1 value no clue where the issue is?

SELECT  MAX(te.StoreID) AS StoreID, 
        SUM(te.Price * te.Quantity) AS Sales, 
        SUM(te.Cost * te.Quantity) AS Cost, 
        COUNT(DISTINCT t.TransactionNumber) AS Trxn, 
        SUM(te.Quantity) AS Quantity 
FROM    TransactionEntry te 
        INNER JOIN [Transaction] t 
            ON te.TransactionNumber = t.TransactionNumber 
                AND te.StoreID = t.StoreID 
        LEFT JOIN item i 
            ON te.itemID = i.ID 
        LEFT JOIN Department d 
            ON i.DepartmentID = d.ID 
WHERE   d.ID <> 8 AND DATEDIFF(day, t.Time, GETDATE()) = 1 
        AND t.WebInvoiceID <> (select WebInvoiceID from [Transaction] where WebInvoiceID>0) 
GROUP   BY te.StoreID

Can anyone help me with this?

Upvotes: 0

Views: 38

Answers (1)

John Woo
John Woo

Reputation: 263723

The error is in this line:

AND t.WebInvoiceID <> (select WebInvoiceID from [Transaction] where WebInvoiceID > 0 )

One way to fix this is to use NOT IN since the subquery returns multiple rows.

AND t.WebInvoiceID NOT IN (select WebInvoiceID from [Transaction] where WebInvoiceID>0)

Another way is by using NOT EXISTS which I preferred more

WHERE   d.ID <> 8 AND DATEDIFF(day, t.Time, GETDATE()) = 1 
        AND NOT EXISTS 
        (
            SELECT 1
            FROM [Transaction] tr
            WHERE t.WebInvoiceID = tr.WebInvoiceID
                AND tr.WebInvoiceID > 0
        )

If not mistaken, based from your original logic, you don't a subquery to filter out WebInvoiceID which are greater than zero. This can be simplified as:

WHERE   d.ID <> 8 AND DATEDIFF(day, t.Time, GETDATE()) = 1 
        AND t.WebInvoiceID > 0

Upvotes: 1

Related Questions