I have an interesting problem here that I'm not sure is possible to solve without restructuring the database I'm working with, but here goes...
I have a query that needs to select information in the following format: "SKU", "Product Category", "Product Sub Category", "On Hand", "Location", "Allocated", "Available", "Receiving This Month"
I have every column working fine excluding the "Receiving This Month" column, which is a bit complicated. For this column I need to use the SKU value (called fishbowlinventoryavailabilitybylg.part) to get a sum of every row of a column in the "shipments" table where the column has the same name as the SKU.
Here would be an example output that may help: http://i.imgur.com/rv7lXxJ.png
Here is my code to generate the first 7 columns:
fishbowlinventoryavailabilitybylg.part as SKU,
domo_productcosts_xlsx_costs.`product category` AS `Product Category`,
domo_productcosts_xlsx_costs.`product sub category` AS `Product Sub Category`,
fishbowlinventoryavailabilitybylg.qty AS `On Hand`,
fishbowlinventoryavailabilitybylg.LG AS Location,
fishbowlinventoryavailabilitybylg.allocated AS `Allocated`,
fishbowlinventoryavailabilitybylg.qty - fishbowlinventoryavailabilitybylg.allocated AS `Available`,
You can't use variables (such as values in a SKU column) as column names in MySQL or most other dialects of SQL. For this reason, most database designers avoid that kind of approach to laying out tables.
Most programmers who need to do such things write programs to generate the SQL queries they need. The built-in table called
information_schema.columns can come in hand for that.