How do i sub query two joins?

Question

I have created two queries that both return the required results independent of each other. I am trying to join them to have the returned values be customerName, Amount Ordered, and Amount Paid.

Currently, this query works but only returns the customerName. How can it get the query to return the other two columns?

SELECT c1.customerName
FROM 
    (SELECT cc.customerName, ROUND(SUM(od.priceEach * od.quantityOrdered), 2) as '$ Amount Ordered'
     FROM customers cc
     INNER JOIN orders o ON o.customerNumber = cc.customerNumber
     INNER JOIN orderdetails od ON od.orderNumber = o.orderNumber
     GROUP BY cc.customerName
    ) c1
 INNER JOIN
    (SELECT c.customerName, ROUND(SUM(p.amount), 2) as 'Total $ Amount Paid'
     FROM customers c
     INNER JOIN payments p ON p.customerNumber = c.customerNumber
     GROUP BY c.customerName
    ) c2
WHERE c1.customerName = c2.customerName
GROUP BY c1.customerName
ORDER BY c1.customerName;

Answer 1

simply add them to the select section:

SELECT c1.customerName, C1.amountOrdered, C2.amountPaid FROM ...

And one more word of advice - DONT use whitespace or special signs like $ in your column names, it is bad practice. I think it's a mistake that mySql even allows it

Answer 2

this should select the others column

SELECT c1.customerName, c1.Amount_Ordered as '$ Amount Ordered', c2.Total_Amount_Paid  as 'Total $ Amount Paid'
FROM 
    (SELECT cc.customerName, ROUND(SUM(od.priceEach * od.quantityOrdered), 2) as Amount_Ordered
     FROM customers cc
     INNER JOIN orders o ON o.customerNumber = cc.customerNumber
     INNER JOIN orderdetails od ON od.orderNumber = o.orderNumber
     GROUP BY cc.customerName
    ) c1
 INNER JOIN
    (SELECT c.customerName, ROUND(SUM(p.amount), 2) as  Total_Amount_Paid
     FROM customers c
     INNER JOIN payments p ON p.customerNumber = c.customerNumber
     GROUP BY c.customerName
    ) c2
WHERE c1.customerName = c2.customerName
GROUP BY c1.customerName
ORDER BY c1.customerName;