Posted: Fri Sep 05, 2008 12:14 pm Post subject: Sum function on one-to-many relationship
I have 2 tables Parent table A & Child table B
Parent has 1 row per employee, child can have multiple rows, there are amount fields in both tables. If I try to join the tables and
use SUM function, the total amount for Child table is correct, but Parent table is wrong (because I think it tries to add the same amount as many times the
child rows related to the parent are present). How can I resolve this?
Below the sum of B table is okay, but A table is not.
Code:
SELECT A.P10_XWAR_DIST,
DIGITS (A.XWAR_MM)||'/'||
DIGITS (A.XWAR_DD)||'/'||
DIGITS (A.XWAR_CC)||
DIGITS (A.XWAR_YY) AS ISSUE_DATE,
SUM(DECIMAL(A.FWT,7,2)) AS FWT_GR,
SUM(DECIMAL(A.STATE_GROSS,7,2)) AS SWT_GR,
SUM(DECIMAL(A.MEDI_GROSS,7,2)) AS MEDI_GR,
SUM(DECIMAL(A.FICA,7,2)) AS OSDI_GR,
SUM(DECIMAL(A.SDI_GROSS,7,2)) AS SDI_GR,
SUM(DECIMAL(A.SUT_GROSS,7,2)) AS SUT_GR,
SUM(DECIMAL(A.NET,7,2)) AS NET_PAY,
SUM(DECIMAL(B.FRNG_TAX_AMT,7,2) AS FRNG
FROM HRUNT.TABLE A,
HRUNT.TABLE B
WHERE A.DIST = B.DIST
AND A.ID_NUMBER = B.ID_NUMBER
AND A.TIMESTAMP = B.TIMESTAMP
AND DIGITS (A.XWAR_MM)||'/'||
DIGITS (A.XWAR_DD)||'/'||
DIGITS (A.XWAR_CC)||
DIGITS (A.XWAR_YY) = '07/03/2008'
AND A.DIST = '99999'
AND A.SCHED_NUM = 'XXX'
AND B.FRNG_SOURCE_CODE <> 'N'
GROUP BY A.DIST,
A.XWAR_MM,A.XWAR_DD,A.XWAR_CC,A.XWAR_YY
If i am guessing ur req. correctly, You want to SUM all recs in parent table and u also want to sum respective Child table records for each parent table rec.
Joined: 08 Aug 2007 Posts: 291 Topics: 2 Location: Chicago
Posted: Thu Sep 11, 2008 11:48 am Post subject:
I assumed that there are really 2 different tables, TABLEA and TABLEB. This should be close to what you're looking for. You list the cols that you want SUM'd from the parent, and select the SUM value from the child.
Code:
SELECT A.P10_XWAR_DIST,
DIGITS (A.XWAR_MM)||'/'||
DIGITS (A.XWAR_DD)||'/'||
DIGITS (A.XWAR_CC)||
DIGITS (A.XWAR_YY) AS ISSUE_DATE,
SUM(DECIMAL(A.FWT,7,2)) AS FWT_GR,
SUM(DECIMAL(A.STATE_GROSS,7,2)) AS SWT_GR,
SUM(DECIMAL(A.MEDI_GROSS,7,2)) AS MEDI_GR,
SUM(DECIMAL(A.FICA,7,2)) AS OSDI_GR,
SUM(DECIMAL(A.SDI_GROSS,7,2)) AS SDI_GR,
SUM(DECIMAL(A.SUT_GROSS,7,2)) AS SUT_GR,
SUM(DECIMAL(A.NET,7,2)) AS NET_PAY,
(SELECT SUM(DECIMAL(B.FRNG_TAX_AMT,7,2)
FROM HRUNT.TABLEB B
WHERE A.DIST = B.DIST
AND A.ID_NUMBER = B.ID_NUMBER
AND A.TIMESTAMP = B.TIMESTAMP
AND B.FRNG_SOURCE_CODE <> 'N' ) AS FRNG
FROM HRUNT.TABLEA A,
WHERE DIGITS (A.XWAR_MM)||'/'||
DIGITS (A.XWAR_DD)||'/'||
DIGITS (A.XWAR_CC)||
DIGITS (A.XWAR_YY) = '07/03/2008'
AND A.DIST = '99999'
AND A.SCHED_NUM = 'XXX'
GROUP BY A.DIST,
A.XWAR_MM,A.XWAR_DD,A.XWAR_CC,A.XWAR_YY
Joined: 08 Aug 2007 Posts: 291 Topics: 2 Location: Chicago
Posted: Thu Sep 11, 2008 12:11 pm Post subject:
You need to be aware that the SQL I posted isn't technically identical to the SQL you started with. Your SQL is an INNER JOIN between TABLEA and TABLEB. Mine is more of a LEFT OUTER JOIN. With the modified version, the SQL will return parent rows with no children. If that's unacceptable, add the SELECT SUM to the col list, but don't remove the INNER JOIN from the original statement.
Joined: 01 Mar 2007 Posts: 475 Topics: 9 Location: Welsh Wales
Posted: Fri Sep 12, 2008 1:26 am Post subject:
Sqlcode,
Parent has 1 row per employee,
As stated by the OP. Take a look at your table A (parent table) and spot the difference.
Could you not perform a summary of table B by employee number and then merge the tables. May not be the best method, but may well work accurately. _________________ If it's true that we are here to help others,
then what exactly are the others here for ?
Expat,
I asked that question because of theinitial query by lacoe1,
Query is summing up different columns from PARENT Table A.
Code:
SUM(DECIMAL(A.FWT,7,2)) AS FWT_GR,
SUM(DECIMAL(A.STATE_GROSS,7,2)) AS SWT_GR,
SUM(DECIMAL(A.MEDI_GROSS,7,2)) AS MEDI_GR,
SUM(DECIMAL(A.FICA,7,2)) AS OSDI_GR,
SUM(DECIMAL(A.SDI_GROSS,7,2)) AS SDI_GR,
SUM(DECIMAL(A.SUT_GROSS,7,2)) AS SUT_GR,
SUM(DECIMAL(A.NET,7,2)) AS NET_PAY,
If there is only one row per employee why would we need to sum up PARENT Table columns?
My understanding of the problem is that query is summing up PARENT TABLE records and hence data from parent table is not populated correctly.
Joined: 08 Aug 2007 Posts: 291 Topics: 2 Location: Chicago
Posted: Fri Sep 12, 2008 9:26 am Post subject:
Yes. There is only one parent row per child. But the result set is not grouped by employee. It is grouped by date and district. When the op coded the INNER JOIN from the parent to the child, the result was that the parent values were replicated for each child row in the results set. The query I posted will return the correct results for the date/district rollup.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
