Query to Return each product name with its amount due, paid, cancelled, and refunded

Question

Three table are given to get the product details such as product_name, due_amount,paid_amount,cancelled,refunded. Three table with their schema:

Product

Column_name	Column_type	Key/NULL
id	int	pk
SKU	varchar(32)
product_name	varchar(128)
product_desc	text
current_price	decimal(8,2)
qty_in_stock	int

Invoice

Column_name	Column_type	Key/NULL
id	int	pk
invoice_number	int
customer_id	int
user_account_id	int
total_price	decimal(8,2)
time_issued	varchar	N
time_due	varchar	N
time_paid	varchar	N
time_cancelled	varchar	N
time_refunded	varchar	N

Invoice_item

Column_name	Column_type	Key/NULL
id	int	pk
invoice_id	int	fk
product_id	int	fk
quantity	int	fk
price	decimal(8,2)
line_total_price	decimal(8,2)

I tried in this way:

SELECT Distinct product, amount_due,amount_paid,cancelled_prod,refund_prod
    FROM   (SELECT p.product_name AS product,
                    ((p.current_price * ii.quantity) - (ii.quantity * ii.price)) AS amount_due,
                    ( ii.quantity * ii.price )  AS amount_paid,
                    'N/A' AS Cancelled_prod,
                    'N/A' AS refund_prod
            FROM   product p
            INNER JOIN invoice_item ii
            ON p.id = ii.product_id
            INNER JOIN invoice i
            ON i.id = ii.invoice_id
            WHERE  i.time_due IS NOT NULL

            UNION

            SELECT p.product_name AS product,
                    0 AS amount_due,
                    ( ii.quantity * ii.price ) AS amount_paid,
                   'N/A' AS Cancelled_prod,
                   'N/A' AS refund_prod
            FROM   product p
            INNER JOIN invoice_item ii
            ON p.id = ii.product_id
            INNER JOIN invoice i
            ON i.id = ii.invoice_id
            WHERE  i.time_paid IS NOT NULL

            UNION

            SELECT p.product_name AS product,
                    0 AS amount_due,
                    0 AS amount_paid,
                    'cancelled' AS Cancelled_prod,
                    'N/A' AS refund_prod
            FROM   product p
            INNER JOIN invoice_item ii
            ON p.id = ii.product_id
            INNER JOIN invoice i
            ON i.id = ii.invoice_id
            WHERE  i.time_cancelled IS NOT NULL

            UNION

            SELECT p.product_name AS product,
                    0 AS amount_due,
                    0 AS amount_paid,
                    'N/A' AS Cancelled_prod,
                    'refunded' AS refund_prod
            FROM   product p
            INNER JOIN invoice_item ii
            ON p.id = ii.product_id
            INNER JOIN invoice i
            ON i.id = ii.invoice_id
            WHERE  i.time_refunded IS NOT NULL
        ) Z
    ORDER  BY product ASC



Is there a better way to do and also i am not sure whether the query will give correct result?

Answer 1

select product_name, sum(amount_due), sum(amount_paid), sum(amount_cancelled), sum(amount_refunded) 
from (
select product_name,
case when time_due is not null then line_total_price end amount_due,
case when time_paid is not null then line_total_price end amount_paid,
case when time_cancelled is not null then line_total_price end amount_cancelled,
case when time_refunded is not null then line_total_price end amount_refunded
from product a left join invoice_item c on a.id = c.product_id
left join invoice b on b.id = c.invoice_id 
) temp_table 
group by product_name order by product_name