mysql use sum and max to find the highest paid invoice

2885 views mysql
6

i am trying to figure out how to get the MAX result of a SUM of values from a table in order to display the highest paid amount of a receipt. I also supposed to use subselect, but im getting confused. So far this query gets me everything i need displayed except for the max value. This is the table (invoice+OD) need to be used This is what i am currently gettingThis is what i should be getting

where can i add MAX() (of sum)?

SELECT invoicenumber invoice
     , invoicedate date
     , SUM(orderqty*ordersell) AS 'Invoice Total' 
  FROM invoice 
  JOIN orderdetail 
    ON invoice.invoiceid = orderdetail.invoiceid 
 GROUP 
    BY invoice ;

answered question

Use a proper GROUP BY. All non-aggregated columns must be in the GROUP BY.

1 Answer

1

If you are using SUM or MAX in SELECT, you need to GROUP BY other columns:

SELECT invoicenumber AS invoice
 , invoicedate AS date
 , SUM(orderqty*ordersell) AS 'Invoice Total' 
FROM invoice 
JOIN orderdetail ON invoice.invoiceid = orderdetail.invoiceid 
GROUP BY invoicenumber,invoicedate;

posted this

Have an answer?

JD

Please login first before posting an answer.