How to count Leadfinish where have values and not null?

Transact-SQL
,
1

I work on SQL server 2012 I Face issue I can't count values on Lead finish in case of no null
so How to do that Please ?
so suppose I have values on LeadFinishPlatingID as :slight_smile:

     companyID    LeadFinishPlatingID `
       345               12333
       345                45678
       345               23323
       345               NULL

Then count for leadfinishPlatingID will be 3 for company id 345

so i need to modify sql script below to count only not null on LeadFinishPlatingId based on company id
SELECT
Companies.DisplayName CompanyName,
ISNULL(Data.CNt, 0) AS TotalPartsCount,
ISNULL(Data.CNt - ISNULL(df.ManCount, 0), 0) AS [Parts Difference],
ISNULL(df.ManCount, 0) ManufacturingPartsCount,
ISNULL(df.[LeadFinishPlating], 0) [LeadFinishPlating]

     FROM
     (
     SELECT CompanyID, COUNT(PartID) CNt 
     FROM Parts.Nop_Part
     GROUP  BY CompanyID
     )AS Data
     LEFT JOIN 
     (
     SELECT 
     p.CompanyID,
     COUNT(m.PartID) ManCount,
     COUNT(m.LeadFinishPlatingID) as [LeadFinishPlating]
        
     from Parts.ManufacturingData m
     LEFT JOIN Parts.Nop_Part p
     ON p.PartID = m.PartID
     GROUP  BY p.CompanyID
     )AS DF ON Df.CompanyID = Data.CompanyID
     LEFT JOIN 
     (
     SELECT c.CompanyComID, C.DisplayName 
     FROM dbo.core_company c 
     )AS Companies ON Companies.CompanyComID = Data.CompanyID
2

add a where clause to your query
where LeadFinishPlating is not null

3

thank you for reply
ok i can do that but I have really six column
so (id1 is not null and id2 is not null and id3 is not null )
my question if i using and to multiple column are this will affect performance
or what i do

4

If you have performance problems,you can create a filtered index on those columns and check the performance.

5

hi

please see link if helps