Here is my query:
select product_name, (sum(item_price - discount_amount) * (quantity)) AS product_total
from products inner join order_items
using(product_id)
group by product_name
My query's output:
Expected Output:
I got almost everything to display but Gibson Les Paul product_total is wrong and the last row doesn't show the Null with its product_total.
An inner join means that you will only see order_items that have a product_name. Use an outer join to see order_items that do not have a product_name. Also, your sum should be the outer most function. Maybe something like:
select product_name, sum((item_price - discount_amount) * (quantity)) AS product_total
from order_items left join products
on order_items.product_id=products.product_id
group by product_name
If needed, look up an explanation of inner and outer joins, like this question for example.
But, looking a little closer at your data, the type of join is probably not the issue for the null row. The bottom row is merely a total of all the other rows, so adding with rollup after the group by clause would give you that row.
