我是一个新手,试图在这里解决这个问题,到目前为止还没有运气,非常感谢任何帮助!
Select Distinct
(AB.agency_no || '-' || ab.branch_no) AS "AGENCY-BRANCH",
count (AB.agency_no || '-' || ab.branch_no) AS Occurences,
A.AGY_NAME AS AGENCY,
Sum(AB.annual_premium) as Premium
From Agency_Book_View AB, Agency A, Branch B
Where AB.agency_no = A.Agency_No
AND B.EXPIRATION_DATE = TO_DATE('12-31-2078', 'MM-DD-YYYY')
AND B.EFFECTIVE_DATE <= sysdate and b.effective_date >=sysdate - 364
Group by AB.agency_no || '-' || ab.branch_no, A.Agy_Name, ab.annual_premium
Order by AB.agency_no || '-' || ab.branch_no
因此,我试图返回每个“代理分支机构”的年度总保费,并且我多次出现代理分支机构。我正在尝试为每个代理分支机构分配一条线路。我希望这一点很清楚。我尝试包含结果集,但不允许在我的帖子中包含图片。
非常感谢!
Brad
尝试这个 :
SELECT (AB.agency_no || '-' || AB.branch_no) AS "AGENCY-BRANCH",
COUNT(AB.agency_no || '-' || AB.branch_no) AS Occurences,
A.AGY_NAME AS AGENCY,
SUM(AB.annual_premium) AS Premium
FROM Agency_Book_View AB, Agency A, Branch B
WHERE AB.agency_no = A.Agency_No AND AB.branch_no = B.branch_no
AND B.EXPIRATION_DATE = TO_DATE('12-31-2078', 'MM-DD-YYYY')
AND B.EFFECTIVE_DATE <= SYSDATE AND B.effective_date >= SYSDATE - 364
GROUP BY AB.agency_no || '-' || AB.branch_no, A.Agy_Name
ORDER BY AB.agency_no || '-' || AB.branch_no
我加入了B表和AB表,删除了DISTINCT和GROUPed BY ab.annual_premium。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)