LEFT JOIN Counting the wrong value-CodePudding

Top of report will show a summary - First row of report summary shows ingot #. Second row of report summary shows % of total tubes inspected that were rejected for ID defects. Third row of report summary shows % of tubes rejected that are ODD tubes (with tube ID ending in 1, 3, 5, 7, 9). Fourth row of report summary shows % of tubes rejected that are EVEN tubes (with tube ID ending in 0,2,4,6,8).

HERE are the tables I am working with Tube Table

TubeRework Table

SELECT  
Tube.Ingot,
COUNT(DISTINCT Tube.Piece) AS NumberOfTubes,
SUM(CASE 
WHEN Tube.AcceptedBy IS NULL 
THEN 1 
ELSE 0 
END) AS NumberOfTubesRejected,
SUM(CASE 
WHEN Tube.AcceptedBy IS NULL AND RIGHT(Tube.Piece, 1) % 2 = 1
THEN 1 
ELSE 0 
END) AS NumberOfOddTubesRejected,
SUM(CASE 
WHEN Tube.AcceptedBy IS NULL AND RIGHT(Tube.Piece, 1) % 2 = 0
THEN 1 
ELSE 0 
END) AS NumberOfEvenTubesRejected,
SUM(CASE 
WHEN reworkcodecount IN (4,5,6)
THEN 1 
ELSE 0 
END) AS NumberRejectedByIDDefects
FROM dbo.Tube
LEFT JOIN(
SELECT
dbo.TubeRework.Tube_#, dbo.TubeRework.ReworkCode_# AS reworkcodecount
FROM dbo.TubeRework
) AS TubeRework_Grouped ON TubeRework_Grouped.Tube_# = dbo.Tube.Tube_#
GROUP BY Tube.Ingot

I am trying to select the ingot, and count on the total number of tubes, which is coming out correctly.

The Issue starts on my

SUM(CASE 
WHEN Tube.AcceptedBy IS NULL 
THEN 1 
ELSE 0 
END) AS NumberOfTubesRejected

If I delete the LEFT JOIN, It works perfectly as expected, and returns 11 NumberOfTubesRejected, which counts the number of time the AcceptedBy Value is NULL. When I dont delete the LEFT JOIN (As I need it to do my last part to grab and return the ingot and tube# that has an ID DEFECT (4-6 value).

Look below as it returns 16 values for NumberOfTubesRejected when I run my query, it should return 11 total if you count the number of times it is null.

Above Query

I'm having issues figuring out how to left join a table, and be able to still grab the NumberOfTubesRejected value as the correct value.

Any Tips would be great.

The Expected output would be, but I cant include my last search for the NumberOfIdDefects in the rework table.

enter image description here

I hope this clears it up.

I need all this data in one report. Need All this in one reportCheers

LEt me know. Thanks

CodePudding user response：

Just a notepad scribble in a dark tube

SELECT tube.Ingot
, COUNT(tube.Piece) AS NumberOfTubes
, COUNT(CASE 
        WHEN tube.AcceptedBy IS NULL 
        THEN tube.Piece END) AS NumberOfTubesRejected
, COUNT(CASE 
        WHEN tube.AcceptedBy IS NULL
         AND RIGHT(tube.Piece,1)%2 = 0
        THEN tube.Piece END) AS NumberOfEvenTubesRejected,
, COUNT(CASE 
        WHEN tube.AcceptedBy IS NULL 
         AND RIGHT(tube.Piece,1)%2 = 1
        THEN tube.Piece END) AS NumberOfOddTubesRejected
, COALESCE(SUM(rework.NumberRejectedByIDDefects), 0) AS NumberRejectedByIDDefects
FROM dbo.Tube AS tube
LEFT JOIN
(
  SELECT TubeRework.Tube_#
  , COUNT(CASE WHEN ReworkCode.CodeNum in (4,5,6)
          THEN 1 END) AS NumberRejectedByIDDefects
  FROM dbo.TubeRework AS TubeRework
  JOIN dbo.ReworkCode AS ReworkCode 
    ON ReworkCode.ReworkCode_# = TubeRework.ReworkCode_#
  WHERE ReworkCode.CodeNum in (4,5,6)
  GROUP BY TubeRework.Tube_#
) rework ON rework.Tube_# = tube.Tube_#
GROUP BY tube.Ingot