Thursday 19 September 2013

MySQL - Multiply column by value depending on that column

MySQL - Multiply column by value depending on that column

How can I write this.. I have table 'Company' with a column 'Size'. The
size references enums. I need to display the average company size as alias
AS 'AverageEstimatedCompanySize' by substituting column 'Size' when column
'Size' is:
1 = 15
2 = 30
3 = 50
4 = 100
5 = 250
In other words, my table shows company size as either 1, 2, 3, 4 or 5.
While 1 is actually a company size of 15.
This is all part of a bigger query:
SELECT COUNT(DISTINCT(ID)) AS 'Total # of Opps', AVG(Size*?) AS
'AverageEstimatedCompanySize'
FROM persontable AS POJT INNER JOIN opportunity
ON POJT.ID = opportunity.id
WHERE opportunity.TimeStamp >= '2012-01-01' AND opportunity.TimeStamp <=
'2012-12-31' AND POJT.JobTitleID IN
(SELECT Id
FROM job
WHERE CategoryID IN
(SELECT id
FROM job_category
WHERE name IN ('Sc', 'Ma', 'Co', 'En', 'Tr')))

No comments:

Post a Comment