MAX not working as expected in Oracle

I have an SQL query

SELECT spt.paymenttype,
  MAX(nest.paytypetotal) total
FROM sportpaymenttype spt
INNER JOIN (SELECT spt.paymenttype,
              SUM(sod.detailunitprice * sod.detailquantity) paytypetotal
            FROM sportorderdetail sod
            INNER JOIN sportorder so ON so.orderid = sod.orderid
            INNER JOIN sportpaymenttype spt ON spt.paymenttype = so.paymenttype
            GROUP BY spt.paymenttype) nest ON nest.paymenttype = spt.paymenttype
GROUP BY spt.paymenttype;

I expect it to return one row (because of the MAX function) however, it returns 4 rows. I came up with a painful way开发者_StackOverflow中文版 to do it properly but I'm wondering, why the max function is behaving this way?

Also, these are the results, where I only expect the first one

PAYMENTTYPE   TOTAL
Loan          8640.95
Check         147.34
Credit Card   479.93
Cash          25.95

What I was wondering is if there was a better way to do this...

SELECT spt.paymenttype,
  nest.paytypetotal total
FROM sportpaymenttype spt
INNER JOIN (SELECT spt.paymenttype,
              SUM(sod.detailunitprice * sod.detailquantity) paytypetotal
            FROM sportorderdetail sod
            INNER JOIN sportorder so ON so.orderid = sod.orderid
            INNER JOIN sportpaymenttype spt ON spt.paymenttype = so.paymenttype
            GROUP BY spt.paymenttype) nest ON nest.paymenttype = spt.paymenttype
WHERE nest.paytypetotal = (SELECT MAX(nest.paytypetotal)
                           FROM (SELECT spt.paymenttype,
                             SUM(sod.detailunitprice * sod.detailquantity) paytypetotal
                           FROM sportorderdetail sod
                           INNER JOIN sportorder so ON so.orderid = sod.orderid
                           INNER JOIN sportpaymenttype spt ON spt.paymenttype = so.paymenttype
                           GROUP BY spt.paymenttype) nest);

Thanks.

It is behaving that way because you're telling Oracle to group by the paymenttype

If you do a MAX(spt.paymenttype) and remove the GROUP BY than it will work as you want it.

The MAX function is an aggregate. When you use a GROUP BY (in your case, the "GROUP BY spt.paymenttype" at the end), the aggregate applies to each group produced by the GROUP BY, not to the result set as a whole. You did get one result row per payment type, as GROUP BY is supposed to do in the absence of filters.

To get one row, pick the single payment type you want, and add a

HAVING spt.paymenttype = 'FOO'

at the very end of the query. If you want the max value across all paytypetotal values, probably easiest (not necessarily best) to make this whole thing into a subquery and then select from it the largest payment value.