MySQL Query: Using SUM Function with JOINS

join;MySQLsum

Sorry if this has been covered before but I couldnt find an example to my issue

Situation: Say I have a table of sales and want to display them

Step 1)

SELECT payment_id, amount, payment_date
FROM payment
WHERE payment_date >= '2018-01-01' 
AND payment_date <= NOW());

Now I want to sum sales or 'amount
Step 2)

SELECT SUM(amount) as 'Total Amount'
FROM
(SELECT payment_id, amount, payment_date
FROM payment
WHERE payment_date >= '2018-01-01' 
AND payment_date <= NOW()
);

Is it possible to have that 'total amount' as a new row in my first table? Ie join the list of payment details with a total amount figure at the end of the list?

Best Answer

As I understand payment_id is unique (maybe primary). If so:

SELECT payment_id, SUM(amount), payment_date
FROM payment
WHERE payment_date >= '2018-01-01' 
AND payment_date <= NOW())
GROUP BY payment_id WITH ROLLUP;

If it is not unique, or if ONLY_FULL_GROUP_BY enabled:

SELECT payment_id, SUM(amount), payment_date
FROM payment
WHERE payment_date >= '2018-01-01' 
AND payment_date <= NOW())
GROUP BY payment_id, payment_date WITH ROLLUP
HAVING payment_date IS NOT NULL or payment_id IS NULL;

Last query will give wrong result if payment_date can be NULL.

Related Question