在Report Builder 3.0查询到Oracle数据库时，SUM结果加倍

I need to get the Total amount of fees in a table without the “Late Fee” and “Filing Fee” and then the total amount of fees including those if they are present while verifying the correct permit ID. When I try and add the second fee to the query it doubles the fees. Here is what I have so far.

我需要在没有“延迟费用”和“申请费”的表格中获得费用总额,然后在验证正确的许可证ID时,包括那些费用总额。当我尝试将第二笔费用添加到查询中时,它会使费用翻倍。这是我到目前为止所拥有的。

SELECT 
  PERMIT.CODE,
  PERMIT.ID2,
  PERMIT.ID1,
  PERMIT.ID3,
  SUM(ITEM.FEE) AS TotalFee,
FROM 
  PERMIT,
  ITEM
AND PERMIT.CODE        = ITEM.CODE
AND PERMIT.ID1         = ITEM.ID1
AND PERMIT.ID2         = ITEM.ID2
AND PERMIT.ID3         = ITEM.ID3
AND ITEM.DESC           <> 'Late Fee'
AND ITEM.DESC           <> 'Filing Fee'

Which results in correct numbers:

这导致正确的数字:

TOTALFEE
550
350

Yet when I add the second fee to the query:

然而,当我向查询添加第二笔费用时:

SELECT 
  PERMIT.CODE,
  PERMIT.ID2,
  PERMIT.ID1,
  PERMIT.ID3,
  SUM(ITEM.FEE) AS TotalFee,
  SUM(t.FEE) AS Fee,
FROM 
  PERMIT,
  ITEM,
  ITEM t
AND PERMIT.CODE        = ITEM.CODE
AND PERMIT.ID1         = ITEM.ID1
AND PERMIT.ID2         = ITEM.ID2
AND PERMIT.ID3         = ITEM.ID3
AND ITEM.DESC           <> 'Late Fee'
AND ITEM.DESC           <> 'Filing Fee'
AND PERMIT.CODE        = t.CODE
AND PERMIT.ID1         = t.ID1
AND PERMIT.ID2         = t.ID2
AND PERMIT.ID3         = t.ID3 

I get the same two rows, but the numbers are off. Results:

我得到相同的两行,但数字是关闭的。结果:

TOTALFEE | FEE
1650     | 1650
2100     | 2100

What am I doing wrong?

我究竟做错了什么?

1 个解决方案

#1

Your query doesn't have a group by, which should be the case when you use aggregate functions. Also you can get the desired results using conditional aggregation.

您的查询没有分组,使用聚合函数时应该是这种情况。您还可以使用条件聚合获得所需的结果。

SELECT 
 PERMIT.CODE,
 PERMIT.ID2,
 PERMIT.ID1,
 PERMIT.ID3,
 SUM(case when ITEM.DESC <> 'Late Fee' then ITEM.FEE end) AS TotalFee,
 SUM(case when ITEM.DESC <> 'Filing Fee' then ITEM.FEE end) AS Fee
FROM PERMIT JOIN ITEM
ON PERMIT.CODE = ITEM.CODE
AND PERMIT.ID1 = ITEM.ID1
AND PERMIT.ID2 = ITEM.ID2
AND PERMIT.ID3 = ITEM.ID3
GROUP BY PERMIT.CODE, PERMIT.ID2, PERMIT.ID1, PERMIT.ID3