SQL error. Subquery returned more than 1 value

Question

My query worked fine with this:

DECLARE @StartDate DateTime  
DECLARE @EndDate DateTime  
SET @StartDate = FLOOR(CAST(DATEADD(WEEK, -1, GETDATE()) AS FLOAT));
SET @EndDate = CAST(GETDATE() AS FLOAT);          
WITH T1 AS 
(   
    SELECT 
        IA.NodeID, 
        IA.InterfaceID,
        SUM(IA.Availability * IA.Weight) AS Sum1,
        SUM(IA.Weight) AS Sum2
    from InterfaceAvailability IA WITH(NOLOCK)  
    where 
        IA.DateTime between @StartDate and @EndDate     
    group by                
        IA.NodeID,
        IA.InterfaceID
)

SELECT 
    IA.nodeid, IA.interfaceid, IA.NodesVendorIcon, IA.NodeName, 
    IA.InterfaceIcon, IA.InterfaceCaption, 
    IA.Availability
FROM (
    SELECT 
        Nodes.NodeID,
        Interfaces.InterfaceID,
        SUM(Sum1) / SUM(Sum2) AS Availability,
        Interfaces.InterfaceIcon, Interfaces.Caption as InterfaceCaption,
        Nodes.Caption as NodeName,
        Nodes.VendorIcon as NodesVendorIcon

    FROM T1
    INNER JOIN Nodes WITH(NOLOCK) 
ON 
    T1.NodeID = Nodes.NodeID
INNER JOIN Interfaces WITH(NOLOCK) 
ON 
    T1.InterfaceID = Interfaces.InterfaceID
    GROUP BY
        Nodes.NodeID, Interfaces.InterfaceID, Interfaces.InterfaceIcon, Interfaces.Caption, Nodes.Caption, Nodes.VendorIcon
        )
    AS IA 

ORDER BY
    NodeID, InterfaceID

But throws SQL error when I tried to retrieve one more CustomerName column for which I modified my query as:

DECLARE @StartDate DateTime  
DECLARE @EndDate DateTime  
SET @StartDate = FLOOR(CAST(DATEADD(WEEK, -1, GETDATE()) AS FLOAT));
SET @EndDate = CAST(GETDATE() AS FLOAT);          
WITH T1 AS 
(   
    SELECT 
        IA.NodeID, 
        IA.InterfaceID,
        SUM(IA.Availability * IA.Weight) AS Sum1,
        SUM(IA.Weight) AS Sum2,
                                N.CustomerName as CustomerName
    from InterfaceAvailability IA WITH(NOLOCK) join Nodes N 
            on IA.NodeID = N.NodeID 

    where 
        IA.DateTime between @StartDate and @EndDate     
    group by                
        IA.NodeID,
        IA.InterfaceID, N.CustomerName
)

SELECT 
    IA.nodeid, IA.interfaceid, IA.NodesVendorIcon, IA.NodeName, 
    IA.InterfaceIcon, IA.InterfaceCaption, 
    IA.Availability,
                (Select Nodes.CustomerName from Nodes)
FROM (
    SELECT 
        Nodes.NodeID,
        Interfaces.InterfaceID,
        SUM(Sum1) / SUM(Sum2) AS Availability,
        Interfaces.InterfaceIcon, Interfaces.Caption as InterfaceCaption,
        Nodes.Caption as NodeName,
        Nodes.VendorIcon as NodesVendorIcon

    FROM T1
    INNER JOIN Nodes WITH(NOLOCK) 
ON 
    T1.NodeID = Nodes.NodeID
INNER JOIN Interfaces WITH(NOLOCK) 
ON 
    T1.InterfaceID = Interfaces.InterfaceID
    GROUP BY
        Nodes.NodeID, Interfaces.InterfaceID, Interfaces.InterfaceIcon, Interfaces.Caption, Nodes.Caption, Nodes.VendorIcon
        )
    AS IA 

Please help. Thank you

Answer 1

You can write a query as:

SELECT 
    IA.nodeid, IA.interfaceid, IA.NodesVendorIcon, IA.NodeName, 
    IA.InterfaceIcon, IA.InterfaceCaption, 
    IA.Availability,
    IA.CustomerName 
FROM (
    SELECT 
        Nodes.NodeID,
        Interfaces.InterfaceID,
        SUM(Sum1) / SUM(Sum2) AS Availability,
        Interfaces.InterfaceIcon, Interfaces.Caption as InterfaceCaption,
        Nodes.Caption as NodeName,
        Nodes.VendorIcon as NodesVendorIcon,
        Nodes.CustomerName 
    FROM T1
    INNER JOIN Nodes WITH(NOLOCK) 
    ON 
    T1.NodeID = Nodes.NodeID
    INNER JOIN Interfaces WITH(NOLOCK) 
    ON 
    T1.InterfaceID = Interfaces.InterfaceID
    GROUP BY
    Nodes.NodeID, Interfaces.InterfaceID, Interfaces.InterfaceIcon,
    Interfaces.Caption, Nodes.Caption, Nodes.VendorIcon
    ,Nodes.CustomerName 
        )
    AS IA 

Answer 2

If i understand correctly the problem is with this line

(Select Nodes.CustomerName from Nodes)

As far as i know you can't have indeterminate number of columns(mayby with pivot tables, but I never used it). So you can't just add select as a column. What can you do is using select which always returns single value(or NULL). So if you use

(Select TOP 1 Nodes.CustomerName from Nodes)

and probably where clause with which you can select the row that you need. I'm not sure if single value is enough for you, if not probably try googling pivot tables in t-sql, however I'm not sure if it does what i think it does.