How to accurately SUM distinct rows in SQL query

Question

I'm trying to generate a report that shows how many Expense Reports (ERs) and the total of those reports for each employee for FY18. The issue I'm experiencing now is the SUM function. The way it is set now, it is summing distinct values in the field for each individual, not necessarily all the values for distinct rows. If I take the "distinct" out of sum(distinct X.ER_TOTAL_EXPENSE), that includes a lot of duplicates in the SUM result. I think it has something to do with my joins. How could I alter those to produce the desired result? I tried to follow the suggestions for this question- How do I SUM DISTINCT Rows?, but it did not work. Here's my current code:

select x.er_employee_number "UIN", x.er_user_full_name, p.PEBEMPL_COAS_CODE_HOME, count (distinct X.ER_DOCUMENT_ID) "Number of ERs", sum(distinct X.ER_TOTAL_EXPENSE) "ER Total"
from INFOR.ALER_EXPENSE_EXPORT_HEADER@TEMPROD x
LEFT JOIN spriden@REPTPROD s on x.ER_Employee_NUMBER = s.spriden_id 
LEFT JOIN pebempl@REPTPROD p on s.SPRIDEN_PIDM = p.pebempl_pidm 
where X.EXPORT_HDR_STATUS like '4' 
and X.ER_PURPOSE like 'Employee%'
and NEW_TIME(X.EXPORT_HDR_STATUS_DATETIME, 'GMT', 'CST') > to_date('1-Jul-2017')
and NEW_TIME(X.EXPORT_HDR_STATUS_DATETIME, 'GMT', 'CST') < to_date('30-Jun-2018')
group by x.er_employee_number, x.er_user_full_name, p.PEBEMPL_COAS_CODE_HOME;

Here is an example of the results: The results on the left are accurate, the results on the right are what the above SQL generates. The ER Total is off by 24. That is because that employee had 2 transactions for $24.

share data and expected result it would be helpful to solve the problem — Zaynul Abadin Tuhin, Aug 15 '18 at 17:06
Without count (distinct) there are duplicate ERs included in the result set. Same goes for the sum(distinct). Sum(distinct), however also returns an incorrect result because it does not count a transaction amount if the same value appears twice for that employee. For example, if there are multiple $25 transactions, it will only count the first. — Katie, Aug 15 '18 at 17:12
Without data its difficult to say - but it looks like your bridge join with spriden@reptprod could be working with multiple acceptable matches - does the table have active/inactive flags or otherwise duplicate values? — Hatt, Aug 15 '18 at 17:16
INFOR.ALER_EXPENSE_EXPORT_HEADER has multiple rows per employee. Each row represents a different Expense Report. I edited the original post to include an example of the data the query generated. — Katie, Aug 15 '18 at 17:19
A couple things - from your question yesterday, you should take @Juan Carlos Oropeza advice - it will help you receive more answers. Also, I know its not data we can work with, but images are generally frowned upon - this should help, https://ozh.github.io/ascii-tables/ . You should also tag which db you're using. In your image - have you investigated why there are 2? Should they both be included? If not, is there an adjustment flag or something else going on? — Hatt, Aug 15 '18 at 17:23
The data on the left is correct--that was generated from original SQL I had, but I then needed to add that PEBEMPL_COAS_CODE_HOME column and from there I experienced errors. I know the original data (data on left) is correct because I manually checked the information in the table and it matches--there were 15 total ERs in that date range totally 677. Duplicate values are fine, they just mean they spent that same amount of money on 2 separate occasions. The problem has to be in the join somewhere. — Katie, Aug 15 '18 at 17:31
Right - well guessing what's going on is not very efficient - you'll need to provide sample data or maybe the schema for each table. — Hatt, Aug 15 '18 at 17:35
Removing the `DISTINCT` will not have any effect on the number or result rows — Amit, Aug 15 '18 at 17:47

score 0 · Answer 1 · answered Aug 15 '18 at 17:42

You can make an view or a derived table.

select x.er_employee_number "UIN", x.er_user_full_name, p.PEBEMPL_COAS_CODE_HOME, grouped.ER_DOCUMENT_ID "Number of ERs", grouped.ER_TOTAL_EXPENSE "ER Total"
from INFOR.ALER_EXPENSE_EXPORT_HEADER@TEMPROD x
LEFT JOIN spriden@REPTPROD s on x.ER_Employee_NUMBER = s.spriden_id 
LEFT JOIN pebempl@REPTPROD p on s.SPRIDEN_PIDM = p.pebempl_pidm 
left join (
    SELECT
        ER_DOCUMENT_ID,sum(ER_TOTAL_EXPENSE) as [ER_TOTAL_EXPENSE]
    FROM INFOR.ALER_EXPENSE_EXPORT_HEADER
    group by ER_DOCUMENT_ID
) grouped ON grouped.ER_DOCUMENT_ID=x.ER_DOCUMENT_ID
where X.EXPORT_HDR_STATUS like '4' 
and X.ER_PURPOSE like 'Employee%'
and NEW_TIME(X.EXPORT_HDR_STATUS_DATETIME, 'GMT', 'CST') > to_date('1-Jul-2017')
and NEW_TIME(X.EXPORT_HDR_STATUS_DATETIME, 'GMT', 'CST') < to_date('30-Jun-2018')
group by x.er_employee_number, x.er_user_full_name, p.PEBEMPL_COAS_CODE_HOME;

How to accurately SUM distinct rows in SQL query

1 Answers1