How to combine the output of two queries in MYSQL when they have non-compatible conditions

Question

I'm looking for some assistance in adding together the output of the two following queries. They have two incompatible conditions so I'm struggling to combine them in the one query.

Total Revenue Year to Date

How to combine the output of both queries?

SELECT 
  sum(datediff(returndate, dueDate)*(products.rentalfee*0.2)) AS 'Late Fees YTD'
FROM products INNER JOIN orderdetails
ON products.productID = orderdetails.productID
WHERE returndate > duedate

And

SELECT 
  sum(products.RentalFee*orderdetails.quantity)
AS 'Total Revenue YTD'
FROM products INNER JOIN orderdetails
ON products.productID = orderdetails.productID
WHERE returndate > duedate OR duedate = returndate

score 1 · Answer 1 · edited Aug 05 '24 at 06:32

You could try using both queries in the select clause of a third query where the table you are selecting from is DUAL. This allows one row to be returned with the results from both queries. For example:

Query

SELECT
  (SELECT 
  sum(datediff(returndate, dueDate)*(products.rentalfee*0.2)) 
  FROM products INNER JOIN orderdetails
  ON products.productID = orderdetails.productID
  WHERE returndate > duedate) AS 'Late Fees YTD'
,(SELECT 
  sum(products.RentalFee*orderdetails.quantity)
  FROM products INNER JOIN orderdetails
  ON products.productID = orderdetails.productID
  WHERE returndate > duedate OR duedate = returndate) AS 'Total Revenue YTD'
FROM DUAL;

Result

Late Fees YTD	Total Revenue YTD
3	4

How to combine the output of two queries in MYSQL when they have non-compatible conditions

1 Answers1

Query

Result