Mysql Query to Calculate Total Products Sold by SKU
I haven't found a similar table structure in existing questions yet so I'm hoping someone may be able to help.
I'm trying to sum up the total amount of products orders and their revenue with the following two table structures:
orders trans_id | account_id | type 1 | 1 | credit 2 | 1 | credit 3 | 1 | void 4 | 2 | credit orderlineitems order_trans_id | sku | quantity | unitPrice 1 | sku1 | 1 | 5 1 | sku2 | 3 | 3 2 | sku1 | 5 | 5 4 | sku1 | 2 | 22.99
So I want to sum up all quantities for a given account_id where the type is credit. So I should have:
Account ID = 1 sku | quantity sku1 | 6 sku2 | 3 Account ID = 2 sku | quantity sku1 | 2
This query is not the identical form you requested, but it does provide you the information you desire.
SELECT o.account_id, oli.sku, oli.quantity FROM orders AS o INNER JOIN orderlineitems AS oli ON o.trans_id = oli.order_trans_id WHERE o.type = 'credit'
AccountId sku quantity 1 sku1 1 1 sku2 3 2 sku1 2