CODEWITHSUNDEEP
sqlsql-serverssms
Albert
Albert

Reputation: 3

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

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

Upvotes: 0

Views: 52

Answers (2)

CR7SMS
CR7SMS

Reputation: 2584

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.

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

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.

Upvotes: 0

Related Questions