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

PDF and MFMailComposeViewController

ios pdf mime-types mfmailcomposeviewcontroll email-attachments

Writing the part of the app that allows the user to generate a pdf and send it. Seems to be working fine. The sent PDF opens fine on the MAC, but on the iPhone it just keeps loading and never opens.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.