CODEWITHSUNDEEP
sqloracle-database
user2363207
user2363207

Reputation:

How to Correctly Sum Totals from a Table That Must be Joined to Another Table that Causes Duplicates

I have two tables like the following:

PAY_TABLE

EMPLID  PAY
123     100
123     150
123     150

DEDUCTION_TABLE

EMPLID  DEDUCTION
123     15
123     30

and I want a result like the following:

TOTAL_PAY
400

I would like to get that result with a fairly simple query and I feel like I'm missing an obvious way to do it, but I can't seem to figure out what is.

For instance, this query returns 800 because every row in the PAY_TABLE is being duplicated when joined to the DEDUCTION_TABLE:

SELECT SUM(PAY) AS TOTAL_PAY 
FROM PAY_TABLE JOIN DEDUCTION_TABLE USING(EMPLID);

And this query returns 250 because the DISTINCT keyword causes the second 150 value in the PAY_TABLE to be ignored:

SELECT SUM(DISTINCT PAY) AS TOTAL_PAY 
FROM PAY_TABLE JOIN DEDUCTION_TABLE USING(EMPLID);

There are probably several ways to do this, but I am looking for the simplest way to return a result of 400.

Here is some code to create the example tables to make it easier:

WITH 
PAY_TABLE AS (
  SELECT 123 AS EMPLID, 100 AS PAY FROM DUAL
  UNION ALL 
  SELECT 123, 150 FROM DUAL
  UNION ALL 
  SELECT 123, 150 FROM DUAL
),
DEDUCTION_TABLE AS (
  SELECT 123 AS EMPLID, 15 AS DEDUCTION FROM DUAL 
  UNION ALL
  SELECT 123, 30  FROM DUAL
)

Upvotes: 0

Views: 42

Answers (2)

Hart CO
Hart CO

Reputation: 34784

It's unclear exactly what you need, since your example doesn't make use of the DEDUCTION_TABLE table, but I believe what you'll want is to aggregate before you JOIN:

;with pay AS (SELECT EmplID,SUM(PAY) AS Pay
              FROM PAY_TABLE
              GROUP BY EmplID
             )
     ,ded AS (SELECT EmplID,SUM(DEDUCTION) AS Ded
              FROM DEDUCTION_TABLE
              GROUP BY EmplID
              )
SELECT *
FROM pay
LEFT JOIN ded
  ON pay.EmplID = ded.EmplID

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132620

Assuming you need the join to DEDUCTION_TABLE just to ensure that there is a deduction for the employee:

SELECT SUM(P.PAY) AS TOTAL_PAY 
FROM PAY_TABLE P
WHERE EXISTS (SELECT NULL FROM DEDUCTION_TABLE D
             WHERE D.EMPLID = P.EMPLID;

Upvotes: 0

Related Questions