CODEWITHSUNDEEP
sqlsumsubquery
Andrew Thompson
Andrew Thompson

Reputation: 13

Sql subquery issue suming subfields

I am trying to get a subquery to work but i cant get past subquery returned more than 1 value. this is not permitted. No matter how i try to re-write this query I cannot get this sum area to work. I am only fairly new to subqueries so any assistance you could provide would be great the query currently stands as

    SELECT        dbo.h.DateDelivery, dbo.h.DateInProduction, dbo.h.JobNumber, dbo.h.JobKeyID, dbo.h.QuantityFrames, dbo.h.QuantityGlass, dbo.h.QuantityPanels, 
                         dbo.r.Description, dbo.r.DivisionID, dbo.r.rKeyID, (SELECT        SUM(t.QtyPacks) AS packqty
FROM            t INNER JOIN
                         h ON t.JobKeyID = h.JobKeyID
WHERE        (t.StageID = 10) OR
                         (t.StageID = 20) OR
                         (t.StageID = 28)
GROUP BY t.JobKeyID)
FROM            dbo.h INNER JOIN
                         dbo.r ON dbo.h.rKeyID = dbo.r.rKeyID
WHERE        (NOT (dbo.r.rKeyID IN (1, 50, 81, 91)))

Upvotes: 0

Views: 40

Answers (1)

Marco Baldelli
Marco Baldelli

Reputation: 3728

You haven't specified which DBMS you are using, however in general a scalar subquery expression must return exactly one column value from one row (see for instance the Oracle documentation).

In your code there is a GROUP BY t.JobKeyID which might cause the subquery to return more than one row.

Upvotes: 0

Related Questions