creating a new column in a MySQL select query

I’ve been really busy since my last post (a whole month without a post!). Work is exceptionally busy and work comes first. Even my reading has slowed down a bit. I have several things waiting to be posted when I get time to write about them – including the first ever competition for this blog! You’ll have to check back over the next week or two to find out what that’s all about, in the mean time this post is all about…

“creating a new column in a MySQL select query”

So, here I am manipulating some data from a supplier to make it work with our internal business systems. Essentially I had to work out how much a kitchen cabinet would cost given an “assembly list”, “component list”, “decision list” and of course “cup of coffee”.

Assembly list tells me that each unit needs a cabinet and a door. There are lots of door colours and lots of cabinet colours. These options are all in the component list which tells me the price of each door. The decision list is my own creation and says “build me a unit using a Maple door and a Maple cabinet”. “cup of coffee” feeds my habit while I code. So far, so good.

Once I’ve created a list of units using maple doors and maple cabinets, I then want to create another list of oak doors and oak cabinets. Join them all together and I have a complete list of cabinets that customers can buy. I needed a way of identifying which cabinet was built with which options and for the life of me I couldn’t remember how to do it. I needed to create an additional column in my query that would record what “decision list” row had been used for this build.

The answer is of course obvious once you know it:
Select “StevesDecision” as StevesColumnName, FirstBuildUpStaticTable.Material, SUM(`Gross weight`) As GWeight, and so on.

“StevesDecision” becomes the content of each row and “StevesColumnName” becomes the column name. All the rest of the select… line remains the same.