Trying a subquery for my first time and not sure how to within an aggregate statement

Question

I am trying to execute the below script but a, unsure how to combine the aggregate select statement.

I am trying to add the subquery to sum the amount of parts within the fsl. I am getting the following errors:

Msg 8120, Level 16, State 1, Line 17 Column 'Customer.CustInvId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Msg 8120, Level 16, State 1, Line 16 Column 'dbo.FSLMaster.FSLId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Can someone please help me be able to execute this? Thank you so much!

P.S.Feel free to tell me I am horrible at explaining and I will do my best to give more details to what I am doing

SELECT c.CustCode
    , na.NatName
    , c.[Name]
    , fm.Code
    , fm.FSLName
    , cfs.SquareFeet AS 'SQFT'
    , CONCAT(a.AddrLine1,',', a.City,',', a.StateAbbr,',', a.ZipCode,',', a.CountryId) AS 'Full Adrress'
    , a.AddrLine1
    , a.City
    , a.StateAbbr
    , a.ZipCode
    , a.CountryId
    , a.Longitude
    , a.Latitude
    ,
    (         
  SELECT ISNULL(SUM(ISNULL(OnHandQty,0) - (ISNULL(ReservedQty,0) )),0) FROM dbo.FSLStock WITH (NOLOCK) WHERE FSLStock.FSLId = FM.FSLId         
  AND PartMasterId IN  ( SELECT PartMasterId FROM dbo.PartMaster P  WITH (NOLOCK) WHERE P.CustInvId=C.CustInvId)) AS IOH 
FROM Customer C WITH(NOLOCK)        
INNER JOIN dbo.CustInvProfile CP WITH(NOLOCK) ON C.CustCode = CP.CustCode AND C.ValidTo IS NULL        
INNER JOIN dbo.CustFSLAssociation CF WITH(NOLOCK) ON CF.CustInvId = CP.CustInvId AND CF.ValidTo IS NULL        
INNER JOIN dbo.FSLMaster FM WITH(NOLOCK) ON FM.FSLId = CF.FSLId AND (COALESCE(FM.ValidTo,getutcdate()) >= getutcdate())       
LEFT JOIN CustFSLStrgDtl CFS WITH(NOLOCK) ON cfs.CustInvId = CF.CustInvId and cfs.FSLId = CF.FSLId
LEFT JOIN [Address] a ON fm.AddrId = a.AddrId
LEFT JOIN NationalAccount na ON c.NatAccountId = na.NatAccountId
LEFT JOIN FSLStock fs ON fm.FSLId = fs.FSLId
GROUP BY c.CustCode, na.NatName, c.[Name], fm.Code, fm.FSLName, cfs.SquareFeet, a.AddrLine1, a.City, a.StateAbbr, a.ZipCode, a.CountryId, a.Longitude, a.Latitude

Does this answer your question? [Column "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"](https://stackoverflow.com/questions/18258704/column-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-aggr) — Peter B, Mar 06 '20 at 15:21
I need the Group by because within each location("FSL"), there are many customers("Name") — Albert, Mar 06 '20 at 17:52

CR7SMS · Answer 1 · 2020-03-06T15:43:44.893

You should be using Group by only when you need some kind of aggregation done at the grouped up levels. You don't actually need the subquery, you can convert it into a join:

SELECT c.CustCode
, na.NatName
, c.[Name]
, fm.Code
, fm.FSLName
, cfs.SquareFeet AS 'SQFT'
, CONCAT(a.AddrLine1,',', a.City,',', a.StateAbbr,',', a.ZipCode,',', a.CountryId) AS 'Full Adrress'
, a.AddrLine1
, a.City
, a.StateAbbr
, a.ZipCode
, a.CountryId
, a.Longitude
, a.Latitude
, SUM(CASE WHEN P.Partmasterid is not null then ISNULL(SUM(ISNULL(OnHandQty,0) - (ISNULL(ReservedQty,0) )),0) else 0 end) as IOH
FROM Customer C WITH(NOLOCK)        
INNER JOIN dbo.CustInvProfile CP WITH(NOLOCK) ON C.CustCode = CP.CustCode AND C.ValidTo IS NULL        
INNER JOIN dbo.CustFSLAssociation CF WITH(NOLOCK) ON CF.CustInvId = CP.CustInvId AND CF.ValidTo IS NULL        
INNER JOIN dbo.FSLMaster FM WITH(NOLOCK) ON FM.FSLId = CF.FSLId AND (COALESCE(FM.ValidTo,getutcdate()) >= getutcdate())       
LEFT JOIN CustFSLStrgDtl CFS WITH(NOLOCK) ON cfs.CustInvId = CF.CustInvId and cfs.FSLId = CF.FSLId
LEFT JOIN [Address] a ON fm.AddrId = a.AddrId
LEFT JOIN NationalAccount na ON c.NatAccountId = na.NatAccountId
LEFT JOIN FSLStock fs ON fm.FSLId = fs.FSLId
LEFT JOIN PartMaster P ON P.CustInvId=C.CustInvId
GROUP BY c.CustCode, na.NatName, c.[Name], fm.Code, fm.FSLName, cfs.SquareFeet, a.AddrLine1, a.City, 
     a.StateAbbr, a.ZipCode, a.CountryId, a.Longitude, a.Latitude

If you think, no aggregation is needed, you can simply remove the group by statement. Hope this helps.

I need the Group by because within each location("FSL"), there are many customers("Name"). This script unfortunately returns this error:Msg 130, Level 15, State 1, Line 21 Cannot perform an aggregate function on an expression containing an aggregate or a subquery. — Albert, Mar 06 '20 at 15:39
I don't think you need the sub query here....I have converted it into a join...Check it out — CR7SMS, Mar 06 '20 at 15:44

score 0 · Answer 2 · answered Mar 06 '20 at 15:40

0

You can use distinct instead to remove duplicates :

select distinct c.CustCode, na.NatName, c.[Name], fm.Code, fm.FSLName,
       . . . 
from Customer C inner join 
     dbo.CustInvProfile CP
     on . . .

Note : NOLOCK reads dirty data (uncommitted). Please be aware.

answered Mar 06 '20 at 15:40

Yogesh Sharma

49,870
5
26
52

Thank you for the advice! I will take this into consideration with future scripts. – Albert Mar 06 '20 at 17:52

Trying a subquery for my first time and not sure how to within an aggregate statement

2 Answers2