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

Answers


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'

it returns:

AccountId   sku   quantity
    1       sku1  1
    1       sku2  3
    2       sku1  2

Need Your Help

While Dynamically creating dictionaries with a reference dictionary, why is the reference dictionaries getting modified? (Python)

python dictionary

I was writing a Python Code that creates dictionaries dynamically,initializes it to a reference dictionary, and modifying a particular value in the dictionary. But,I found that not only I am getting

jQuery : Event not binding in IE 10

javascript jquery jquery-plugins internet-explorer-10

I want to attach a double click event on an ID/Element if there is a single left click on that same ID/Element.