Single occurrence or most recent response while using sum functions

Question

Ok, yesterday I asked a question and got a great answer. I am back for more help and gosh do I need it!!!

Yesterdays question here : Yesterday's Question - had to do with Sum and Distinct functions

-The link is to another question within this site

Today I am adding to the results an address for each row that is returned, the problem is that when I return an address I am finding that people have entered them differently ex. 123 Main ST. or 123 Main Street etc. The question is am I able to pull only the most recent version or single occurrence of said address and still sum premiums as I have successfully done in the link above?

Here is what I have and its not working:

Select (Agency_Book_View.agency_no || '-' || Agency_Book_View.branch_no) AS "AGENCY-BRANCH",
   Agency.Agy_Name,
   SUM(Agency_Book_View.annual_premium) AS Premium,
   Branch.Mail_Address1,
   Agency_Book_View.effective_date
  from Branch
  INNER JOIN Agency_Book_View
on Branch.Agency_No = Agency_book_view.agency_no 
    INNER JOIN Agency
on Branch.Agency_No = Agency.agency_no
 INNER JOIN Comp_Agy on Branch.Agency_No = comp_agy.agency_no 
WHERE COMP_AGY.expiration_date = TO_DATE('12-31-2078','MM-DD-YYYY')
Group by (Agency_Book_View.agency_no || '-' || Agency_Book_View.branch_no),
   Agency.Agy_Name,
   Branch.Mail_Address1,
   Agency_Book_View.Effective_Date
Order by (Agency_Book_View.agency_no || '-' || Agency_Book_View.branch_no)

Result

Again, I truly appreciate any and all help!!!

Brad

Where is the address stored and how do you know what is the most recent? — Gordon Linoff, Dec 12 '12 at 20:21
Address is stored in the Branch table and it honestly doesnt matter if it is most recent. I was using that as a way to differentiate. If there is a better way to pull just one occurrence I am all for it. Thanks! — Brad, Dec 12 '12 at 20:53

score 0 · Answer 1 · answered Dec 12 '12 at 20:23

I suspect the answer looks like this:

Select (Agency_Book_View.agency_no || '-' || Agency_Book_View.branch_no) AS "AGENCY-BRANCH",
       Agency.Agy_Name,
       SUM(Agency_Book_View.annual_premium) AS Premium,
       MAX(case when seqnum = 1 then Agency_Book_View.address end) as address,
       Branch.Mail_Address1,
       Agency_Book_View.effective_date
from Branch INNER JOIN
     (select *,
             ROW_NUMBER() over (partition by agency_no order by AddressDate desc) as seqnum
      from Agency_Book_View
     ) Agency_Book_View
     on Branch.Agency_No = Agency_book_view.agency_no INNER JOIN
     Agency
     on Branch.Agency_No = Agency.agency_no INNER JOIN
        Comp_Agy on Branch.Agency_No = comp_agy.agency_no 
WHERE COMP_AGY.expiration_date = TO_DATE('12-31-2078','MM-DD-YYYY')
Group by (Agency_Book_View.agency_no || '-' || Agency_Book_View.branch_no),
   Agency.Agy_Name,
   Branch.Mail_Address1,
   Agency_Book_View.Effective_Date
Order by (Agency_Book_View.agency_no || '-' || Agency_Book_View.branch_no)

This adds a sequence number into the table that seems like it would have the address, using row_number(). It assumes the presence of a field to determine the most recent; I'm guessing at the name. It uses an aggregation function in the outer select to select the first value.

I am getting back an error after the select *, stating FROM keyword not found where expected. Thanks again Gordon! I know this must be difficult when you cant work with the or have the data in front of you. I couldn't imagine. — Brad, Dec 12 '12 at 20:55

Single occurrence or most recent response while using sum functions

1 Answers1