Sum and distinct dont alter results?

Question

I am a novice trying to work through this here with no luck so far, any help is greatly appreciated!!!

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

So I am trying to return total annual premium per "agency-branch" and I am getting multiple occurrences of agency-branch. I am trying to get one line per agency branch. I hope this is clear. I tried to include a result set but wasnt allowed to include a picture in my post.

Thanks very much!

Brad

I'm quite sure your query lacks a JOIN somewhere. B table is not linked with anything else, which seems wrong here. — xlecoustillier, Dec 11 '12 at 16:35

score 0 · Answer 1 · answered Dec 11 '12 at 16:36

0

I think you need to remove ab.annual_premium from the group by clause.

answered Dec 11 '12 at 16:36

Polly Shaw

2,932
1
15
21

score 0 · Accepted Answer · answered Dec 11 '12 at 16:38

Try this :

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

I joined B table and AB table, removed the DISTINCT and the GROUPed BY ab.annual_premium.

Sum and distinct dont alter results?

2 Answers2

Linked