How can I make an MySQL View for Tax per Order

Question

My data:

How can I get there? I tried to make a MySQL view on my table TAX.

CREATE VIEW TaxSplitPerOrder AS SELECT OrderNumber, tax_invoiced, total_invoiced, created_at, tax_percent, SUM(tax_invoiced_order_item) FROM test GROUP by OrderNumber;

Can anyone help me?

Answer 1

You need to include all of the other non-summarized columns from your SELECT in the GROUP BY clause. You may want to sum total_invoiced too or else you will still have 2 rows for each order number.

Answer 2

The query:

SELECT OrderNumber, tax_invoiced, total_invoiced, created_at, MAX(CASE WHEN tax_percent = 21 THEN tax_invoiced_order_item END) tax_21, MAX(CASE WHEN tax_percent = 6 THEN tax_invoiced_order_item END) tax_6 FROM test GROUP BY OrderNumber;