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

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

CodePudding user response：

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.

CodePudding user response：

Your query gets the maximum purdt and adds up the amount for this date. It also checks whether the maximum purdt is in January or February 2022. If it is, the result gets show, if it is not, you don't show the result. This is not the query you want.

Apart from that, the query looks rather ugly. You are using an ancient join syntax that is hard to read and prone to errors. We used that in the 1980s, but in 1992 explicit joins made it into the SQL standard. You should no longer use this old comma syntax. It is strange to see it still being used. Feels like going to a museum. Then, you are using table aliases. The idea of these is to get a query more readable, but yours even lessen readability, because your alias names a and b are arbitrary. Use mnemonic names instead, e.g. mp for mempur and mi for meminfo. Then, you are comparing the date (I do hope purdt is a date!) with strings. Don't. Use date literals instead.

As to your tables: Are you really storing the age? You will have to update it daily to keep it up-to-date. Better store the date of birth and calculate the age from it in your queries.

Here is a query that gets you the maximum date and the total amount for the given date range:

select memberno, m.name, p.sum_amount, p.max_purdt, m.age
from meminfo m
left outer join
(
  select memberno, sum(amount) as sum_amount, max(purdt) as max_purdt
  from mempur
  where purdt >= date '2022-01-01' and purdt < date '2022-03-01'
  group by memberno
) p using (memberno)
order by memberno;

And here is a query that gets you the maximum overall date along with the total amount for the given date range:

select memberno, m.name, p.sum_amount, p.max_purdt, m.age
from meminfo m
left outer join
(
  select
    memberno,
    sum(case when where purdt >= date '2022-01-01' and purdt < date '2022-03-01'
          then amount
        end) as sum_amount,
    max(purdt) as max_purdt
  from mempur
  group by memberno
) p using (memberno)
order by memberno;