Generate a products quantity sold from each state. Mysql query

Question

This is the end result that I am trying to achieve:

Tally of quantity sold for each product for each state for a specific date (this month for example)

             District State A   District State B 
Product A                 356                934
Product B                 343                232

Scenario

I have the following points that need to be made clear:

• My system is up and running so to change schema around would be difficult until the next update.
• I have districts known as 'States', such as kansas or texas for example.
• I have a table of Products known as 'Products' with no price associated with them
• I have a table consisting of State Product Prices with a link to the 'States' and 'Product' table, with the addition of a 'price column'. So each state have their own prices for the list of products.
• I have customers known as 'Agents' each connected to a specific state so that during the order process (in my application) each agent is charged the correct product price obtained from the state product price table.
• I have an Order table consisting of orders made by Agents with the date that the order was placed recorded as well in the same orders table. This table does not contain the list of products bought. Thats what the next table is there for (orderLines table)
• I have an Orderlines table which stores all the products bought with their quantity sold connected to one order from the orders table. so there can be many orderlines per order.

This being said The following tables look like this:

States table

State_Id
State_Name

Products table

Product_Id
Product_Name

State product prices table

StateProduct_Id
State_Id - referenced to states table
Product_Id - linked to products table
StateProduct_Price

Agents table

Agent_Id
Agent_Name
State_Id - linked to states table

Orders Table

Order_Id
Agent_Id
Order_DatePurchased - in the end this date is what will be used to calculate the total quantity of products sold within a certain date

Order Lines table:

OrderLine_Id 
Order_Id - linked to the orders table
Agent_Id - linked to the agents table
Product_Id - linked to the products table not products state table
OrderLine_ProductNameATOP - at time of purchase
OrderLine_QuantitySoldATOP - at time of purchase

It is the order lines table that will be used to grab the quantity of that specific product sold referenced by the product id which will be used in the report.

I need a query to be able to product the report shown above.

What can i do to get this report?! I hope someone can help. I dont expect someone to do it for me, just an explanation would be great

Answer 1

You need a crosstab report, which isn't the easiest thing to produce in MySQL.

Here is a query (untested) to get your result set for January:

Select 
   OrderLines.Product_Id,
   States.State_Name,
   Sum(OrderLines.OrderLine_QuantitySoldATOP) as Quantity
From ( (
   OrderLines 
      Inner Join Orders on OrderLines.Order_Id = Orders.Order_Id )
      Inner Join Agents on OrderLines.Agent_ID = Agents.Agent_ID )
      Inner Join States on Agents.State_Id = States.State_Id 
Where 
   Orders.Order_DatePurchased >= '2013-01-01' and
   Orders.Order_DatePurchased < '2013-02-01' 
Group by 
   OrderLines.Product_Id,
   States.State_Name

Now you need to format it in a crosstab. This link will bring you to a page that explains how to utilize a stored procedure to turn the result set from the query above to the layout that you require.

http://www.danradigan.com/wordpress/2011/03/pivot-tables-in-mysql/