'SUM() with entries from 2 table

    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.

sql-servertsql


Solution 1:[1]

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.

Solution 2:[2]

You could start by getting the list of distinct PNum and MethIDs to use and then do your summing based on that list:

;WITH entries as (
    SELECT DISTINCT PNum, MethID
        FROM tblOther)
SELECT * 
    FROM entries
    INNER JOIN tbl_plt 
        ON entries.PNum = tbl_plt.PNum 
        AND entries.MethID = tbl_plt.MethID
    GROUP BY entries.PNum

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Randy in Marin
Solution 2

Related Questions