SQLServer Subquery units as column

Question

I'm trying to get a subquery as a column with a condition.

The error I get from SQL Server is:

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

There are a bunch more columns I'm selecting, but here is the main query that is giving ma an error:

SELECT ItemNo, Channel, Brand, TotalUnits,
       (SELECT TotalUnits FROM Table1 WHERE Locked = 1) As LockedUnits
FROM Table1

EDIT: Locked is a 0 or 1 value

Ideally the query would return this (LockedUnits is always a subset of TotalUnits):

ItemNO   Channel   Brand    TotalUnits    LockedUnits
1        5         Adidas   100           80
2        7         Nike     500           360

Answer 1

It looks like what you're trying to do is get TotalUnits only for those rows where Locked = 1. If that's the case, you're better off using case:

SELECT ItemNo, Channel, Brand, TotalUnits,
       case when Locked = 1 then TotalUnits else null end As LockedUnits
FROM Table1

You can use a sub-select to do this, but you need to join the sub-select to the main query. Assuming ItemNo is unique, it would look like this:

SELECT ItemNo, Channel, Brand, TotalUnits,
       (SELECT TotalUnits FROM Table1 t2 WHERE Locked = 1
        WHERE t1.itemno = t2.itemno) As LockedUnits
FROM Table1 t1

Of course, it's kind of silly to hit a single table twice to get values that are already in the row you're returning.

---

Comment Response: The only time you'd prefer the sub-select is if you're getting data from a different table or a different row in the same table. And, even then, I prefer to put the table in the where clause and join it to the main table normally.