How to use SUM and MAX in select statement more than one table

I have 2 Tables

table a mempur      
    
    memberno = member number    
    purdt    = purchase date    
    amount   = purchase amount

table b meminfo

    memberno = member number
    fname    = first name
    age      = age


select a.memberno,b.fname,sum(a.amount),a.purdt,b.age from mempur a,(select max(purdt) as maxdate,memberno from mempur group by memberno) maxresult,meminfo b
where a.memberno=b.memberno
and a.purdt between '01-JAN-22' and '28-FEB-22'
and a.memberno=maxresult.memberno
and a.purdt=maxresult.maxdate
group by a.memberno,b.fname,a.purdt,b.age
order by a.memberno;

How to get my result with total purchase amount and highest date purchase from table mempur? I use this query able to show the result but the total amount incorrect between the range.

Anyone help is much appreciated.

my sample data

MEMBERNO        PURDT              AMOUNT
--------------- --------------- ---------
BBMY0004580     12-AUG-21          823.65
BBMY0004580     12-AUG-21          1709.1
BBMY0004580     26-AUG-21          1015.1
BBMY0004580     28-AUG-21          1105.1

my result only show total amount 1105.1

Solution

You can aggregate in mempur and then join to meminfo:

SELECT i.*, p.total_amount, p.maxdate
FROM meminfo i 
INNER JOIN (
  SELECT memberno, SUM(amount) total_amount, MAX(purdt) maxdate
  FROM mempur 
  WHERE purdt BETWEEN '01-JAN-22' AND '28-FEB-22'
  GROUP BY memberno
) p ON p.memberno = i.memberno;

You may use a LEFT join if there are members with no purchases which you want in the results.