SUM() with entries from 2 table-CodePudding

    Select p.pnum, 
       SUM(CASE WHEN P.NegativeScreen = 'Type99' THEN 1 ELSE 0 END) TotalDetected,
       SUM(IIF(P.IsPositive = 1, 1,0)) TotalP,
       SUM(CASE WHEN (P.MethId NOT IN (4, 8, 10, 25) THEN 1 ELSE 0 END) Total,
       SUM(CASE WHEN (P.MethID IN (34,64) ) THEN 1 ELSE 0 END) TotalVal1,       
       SUM(CASE WHEN (P.MethID IN (16,64) ) THEN 1 ELSE 0 END) TotalVal2,
       SUM(CASE WHEN (P.MethID IN (2,4,6,11,13,14,15,18,21,22,24,28,30,31) OR (P.MethID 
         = 1 AND P.TotalCount IS NOT NULL)) THEN 1 ELSE 0 END) TotalMethOther,     
      FROM tbl_plt p
      GROUP BY P.PNum

Notice that the above query has all the fields from the tbl_plt table and SUM() is done on the fields.

Notice where I have MethID mentioned above. I need to check if those MethID exist in the tbl_plt table and if they exist in another table called TblOther. If so, tally it up accordingly.

Here is the fields in TblOther Table. Note that in TblOther table, we can have multiple PNums but the MethID will be different. Also note that for not all pNums will have entries in the TblOther table.

    ID    PNum   MethID 
    1     232    32
    2     232    64
    3     232    10
    4     104    14
    5     104    54
    6     22     4
    7     4      13

I tried with LEFT JOIN with TblOther table but things gets messy as with the left join, it also tallies up incorrectly for places like:

    SUM(CASE WHEN P.NegativeScreen = 'Type99' THEN 1 ELSE 0 END) TotalDetected,
    SUM(IIF(P.IsPositive = 1, 1,0)) TotalP,

As an example for where I have:

    SUM(CASE WHEN (P.MethID IN (34,64) ) THEN 1 ELSE 0 END)

it needs get the count of how many MethID exist in both the tblOther and tbl_plt for where MethID is 34 or 64 for the associated PNum.

It needs to do similarly for other places where MethID is mentioned.

CodePudding user response：

I don't know enough about TblOther or it's join, but I suspect you might need to do the same group by (i.e., PNum) on it before joining on PNum. Then the left join will match either 0 or 1 records. Be sure to account for the null if there is no match.

CodePudding user response：

You would just need to add a where clause to the query like:

Updated...

WHERE P.MethID in (SELECT DISTINCT MethID FROM TblOther
                      WHERE PNum = P.PNum)