How do I subquery within a case statement?

So here’s my coding

select item_number[Item Number],    
            SUM(CASE WHEN [type] = 0 and location_id not in (select location_id from t_location with(nolock) where location_id like 'GW%' or location_id like 'SW%')    THEN actual_qty ELSE 0 END) AS [DC C1]  
            ,SUM(CASE WHEN [type] = -6 and location_id not in (select location_id from t_location with(nolock) where location_id like 'GW%' or location_id like 'SW%')  THEN actual_qty ELSE 0 END) AS [DC C7]  
            ,SUM(CASE WHEN [type] = -9 and location_id not in (select location_id from t_location with(nolock) where location_id like 'GW%' or location_id like 'SW%')  THEN actual_qty ELSE 0 END) AS [DC C1lock]          
            ,SUM(CASE WHEN [type] = -15 and location_id not in (select location_id from t_location with(nolock) where location_id like 'GW%' or location_id like 'SW%') THEN actual_qty ELSE 0 END) AS [DC C7lock]
            ,SUM(CASE WHEN [type] = 0 and location_id like 'GW%'    THEN actual_qty ELSE 0 END) AS [GW C1]          
            ,SUM(CASE WHEN [type] = -6 and location_id like 'GW%'   THEN actual_qty ELSE 0 END) AS [GW C1]      
            ,SUM(CASE WHEN [type] = -9 and location_id like 'GW%'   THEN actual_qty ELSE 0 END) AS [GW C1lock]      
            ,SUM(CASE WHEN [type] = -15 and location_id like 'GW%'  THEN actual_qty ELSE 0 END) AS [GW C7lock]
            ,SUM(CASE WHEN [type] = 0 and location_id like 'SW%'    THEN actual_qty ELSE 0 END) AS [SW C1]          
            ,SUM(CASE WHEN [type] = -6 and location_id like 'SW%'   THEN actual_qty ELSE 0 END) AS [SW C1]      
            ,SUM(CASE WHEN [type] = -9 and location_id like 'SW%'   THEN actual_qty ELSE 0 END) AS [SW C1lock]      
            ,SUM(CASE WHEN [type] = -15 and location_id like 'SW%'  THEN actual_qty ELSE 0 END) AS [SW C7lock]  
From t_stored_item with(nolock)         
group by item_number

The error code I get is

Msg 130, Level 15, State 1, Line 3 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Msg 130, Level 15, State 1, Line 5 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Msg 130, Level 15, State 1, Line 7 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Msg 130, Level 15, State 1, Line 9 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Now I know the rest of the code is good but the only issue that I’m having right now is because of the subquery when wrote like this. I eventually want all of the inventory within the warehouses we have but I want them to be split up by different warehouses. We gave the precursor warehouse names as GW and SW the rest is just out there as a lot of different aisles within the main warehouse but there’s no real constant there that I can ping off of. The query wrote on it’s own works just fine

select location_id 
from t_location with(nolock) 
where location_id like 'GW%' or location_id like 'SW%'

But when nested into the case statement and summing it altogether then it doesn’t want to pull back any results… The format at the end gives one line per each item number but with different columns for the different warehouses and those are how I want to view the information because it’s much easier to read than to just jump around different lines.

Is there anyway to make this possible? The grouping needs to stay so that everything pulls back but I’m not figuring out an easy way to get this to pull the information.

Answer

Use a join with conditional aggregation:

SELECT
    si.item_number,
    SUM(CASE WHEN si.[type] = 0 AND l.location_id NOT LIKE '[GS]W%'
            THEN l.actual_qty ELSE 0 END) AS [DC C1],
    SUM(CASE WHEN si.[type] = -6 AND l.location_id NOT LIKE '[GS]W%'
            THEN l.actual_qty ELSE 0 END) AS [DC C7],
    SUM(CASE WHEN si.[type] = -9 AND l.location_id NOT LIKE '[GS]W%'
            THEN l.actual_qty ELSE 0 END) AS [DC C1lock],
    SUM(CASE WHEN si.[type] = -15 AND l.location_id NOT LIKE '[GS]W%'
            THEN l.actual_qty ELSE 0 END) AS [DC C7lock],
    SUM(CASE WHEN si.[type] = 0 AND l.location_id LIKE 'GW%'
            THEN l.actual_qty ELSE 0 END) AS [GW C1],
    ...
FROM t_stored_item si
LEFT JOIN t_location l
    ON si.location_id = l.location_id
GROUP BY
    si.item_number;

Advertisement

Answer