SQL - Sum of wages group by other columns

I have a table like below (This is trimmed version, this table contains 11-12 Billion rows with 25 years worth of data. Need to sum gw based on pType as category and separated by combination of eid and cid

eid cid ID pDate pFreq gw PHrs pType
637 163 2037 1/8/21 1 8.13 NULL S
637 163 2037 1/8/21 1 162.5 NULL V
228 787 2037 1/8/21 1 8.13 NULL S
228 787 2037 1/8/21 1 162.5 NULL V
637 163 2037 1/8/21 1 474.5 NULL R
228 787 2037 1/8/21 1 474.5 NULL R
637 163 2037 1/8/21 1 130 NULL H
228 787 2037 1/8/21 1 130 NULL H
637 163 2037 1/15/21 1 602.88 NULL R
228 787 2037 1/15/21 1 602.88 NULL R
637 163 2037 1/22/21 1 32.5 NULL V
228 787 2037 1/22/21 1 619.13 NULL R
637 163 2037 1/22/21 1 619.13 NULL R
228 787 2037 1/22/21 1 32.5 NULL V
228 787 2037 1/27/21 1 300 NULL B
637 163 2037 1/27/21 1 300 NULL B
637 163 2037 1/29/21 1 48.75 NULL V
228 787 2037 1/29/21 1 48.75 NULL V
637 163 2037 1/29/21 1 248.14 NULL O
637 163 2037 1/29/21 1 425.26 NULL R
228 787 2037 1/29/21 1 248.14 NULL O
228 787 2037 1/29/21 1 425.26 NULL R
637 163 2037 1/29/21 1 130 NULL H
228 787 2037 1/29/21 1 130 NULL H
637 163 2037 2/5/21 1 65 NULL S
228 787 2037 2/5/21 1 65 NULL S
637 163 2037 2/5/21 1 587.11 NULL R
228 787 2037 2/5/21 1 587.11 NULL R
228 787 2037 2/12/21 1 617.99 NULL R
637 163 2037 2/12/21 1 617.99 NULL R
637 163 2037 2/19/21 1 635.7 NULL R
228 787 2037 2/19/21 1 635.7 NULL R
637 163 2037 2/26/21 1 654.06 NULL R
228 787 2037 2/26/21 1 654.06 NULL R
637 163 2037 3/5/21 1 162.5 NULL V
228 787 2037 3/5/21 1 489.45 NULL R
637 163 2037 3/5/21 1 489.45 NULL R
228 787 2037 3/5/21 1 162.5 NULL V
637 163 2037 3/12/21 1 24.38 NULL O
637 163 2037 3/12/21 1 650 NULL R
228 787 2037 3/12/21 1 24.38 NULL O
228 787 2037 3/12/21 1 650 NULL R
637 163 2037 3/19/21 1 614.74 NULL R
228 787 2037 3/19/21 1 614.74 NULL R
637 163 2037 3/19/21 1 48.75 NULL S
228 787 2037 3/19/21 1 48.75 NULL S
228 787 2037 3/26/21 1 100.43 NULL O
228 787 2037 3/26/21 1 650 NULL R
637 163 2037 3/26/21 1 100.43 NULL O
637 163 2037 3/26/21 1 650 NULL R


When I run the below query,

SELECT
m.eid,
m.cid,
m.id,
p.pDate,
p.pFreq,
p.PHrs,
SUM(CASE WHEN p.pType
IN ('H','N','R','S','V') THEN p.gw ELSE 0 END) AS gw_r,
SUM(CASE WHEN
p.pType IN ('B','I','') THEN p.gw ELSE 0 END) AS gw_b,
SUM(CASE WHEN
p.pType IN ('O') THEN p.gw ELSE 0 END) AS gw_ot,
SUM(CASE WHEN p.pType
IN ('C','D','E','K','M','P','T','UP','US','UV','W') THEN p.gross_wages ELSE 0 END) AS gw_o

FROM [dbo].[mup] m
JOIN dbo.ppart p on m.id = p.id
JOIN dbo.cli cli on m.cid = cli.cid
JOIN dbo.cldt cd on m.cid = cd.cid
where p.pDate BETWEEN '01/01/2021' AND '06/30/2021'
and p.id = 2037
GROUP BY
m.eid,
m.cid,
m.location,
m.id,
p.pDate,
p.pFreq,
p.PHrs
ORDER BY
p.pDate


Output returning as

eid cid ID pDate pFreq PHrs gw_r gw_b gw_ot gw_o
637 163 2037 1/8/21 1 NULL 775.13 0 0 0
228 787 2037 1/8/21 1 NULL 775.13 0 0 0
228 787 2037 1/15/21 1 NULL 602.88 0 0 0
637 163 2037 1/15/21 1 NULL 602.88 0 0 0
228 787 2037 1/22/21 1 NULL 651.63 0 0 0
637 163 2037 1/22/21 1 NULL 651.63 0 0 0
228 787 2037 1/27/21 1 NULL 0 300 0 0
637 163 2037 1/27/21 1 NULL 0 300 0 0
637 163 2037 1/29/21 1 NULL 604.01 0 248.14 0
228 787 2037 1/29/21 1 NULL 604.01 0 248.14 0
637 163 2037 2/5/21 1 NULL 652.11 0 0 0
228 787 2037 2/5/21 1 NULL 652.11 0 0 0
637 163 2037 2/12/21 1 NULL 617.99 0 0 0
228 787 2037 2/12/21 1 NULL 617.99 0 0 0
637 163 2037 2/19/21 1 NULL 635.7 0 0 0
228 787 2037 2/19/21 1 NULL 635.7 0 0 0
228 787 2037 2/26/21 1 NULL 654.06 0 0 0
637 163 2037 2/26/21 1 NULL 654.06 0 0 0
228 787 2037 3/5/21 1 NULL 651.95 0 0 0
637 163 2037 3/5/21 1 NULL 651.95 0 0 0
637 163 2037 3/12/21 1 NULL 650 0 24.38 0
228 787 2037 3/12/21 1 NULL 650 0 24.38 0
637 163 2037 3/19/21 1 NULL 663.49 0 0 0
228 787 2037 3/19/21 1 NULL 663.49 0 0 0
637 163 2037 3/26/21 1 NULL 650 0 100.43 0
228 787 2037 3/26/21 1 NULL 650 0 100.43 0


Issue is: gw_r, gw_b, gw_ot, gw_o is giving me duplicate results. How do I fix this?

Appreciate all the help/suggestions

Thank you.

Continue reading...