MySQL query select and compute value

Question

SELECT 
if('1000'>plan_limit_max,'1000'-plan_limit_max,0) as 'Overage',
if(`per_hour_cost`='0', `per_period_cost`+'10'*`Overage`, per_hour_cost*720) AS 'Total'

FROM `service_price`

order by Total asc
;

There is an error with Overage at line 3

Error Code: 1054. Unknown column 'Overage' in 'field list'

Is there a way to do what I want with one query? I want to order results by total price, but to calculate it I have to calculate the usage exceeds the limit.

I know there should be other ways to do it, use more than one queries, create views, etc.. I just wonder if there is a simpler query for this.

Why are you single-quoting what look like numeric constants (e.g., '1000') ? — pilcrow
– pilcrow, Commented Oct 17, 2011 at 3:48

pilcrow · Accepted Answer · 2011-10-17 03:54:56Z

1

You can compute Overage once, in a subquery:

SELECT Overage,
       IF(per_hour_cost = 0, per_period_cost + 10 * Overage, per_hour_cost * 720)
         AS Total
  FROM (SELECT IF(1000 > plan_limit_max, 1000 - plan_limit_max, 0) AS Overage,
               service_price.*
          FROM service_price) d;

answered Oct 17, 2011 at 3:54

pilcrow

59.2k14 gold badges101 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sparky · Accepted Answer · 2011-10-17 03:40:19Z

1

Overage is not defined as part of the query, only as the column header in the result. You cannot reference that column as it does not yet exist... You've got to use something like the SQL below:

SELECT 
if('1000'>plan_limit_max,'1000'-plan_limit_max,0) as 'Overage',
if(`per_hour_cost`='0', `per_period_cost`+'10'*if('1000'>plan_limit_max,'1000'-             plan_limit_max,0), 
per_hour_cost*720) AS 'Total'

FROM `service_price`

order by Total asc

answered Oct 17, 2011 at 3:40

Sparky

15.1k2 gold badges34 silver badges45 bronze badges

Comments

Sam Casil · Accepted Answer · 2011-10-17 03:46:24Z

0

You have an error putting " `` " in OVERAGE. You must use ' ' in OVERAGE.

SELECT 
if('1000'>plan_limit_max,'1000'-plan_limit_max,0) as 'Overage',
if(`per_hour_cost`='0', `per_period_cost`+'10'* 'Overage', per_hour_cost*720) AS 'Total'

FROM `service_price`

order by Total asc
;

answered Oct 17, 2011 at 3:46

Sam Casil

9581 gold badge11 silver badges16 bronze badges

1 Comment

pilcrow Over a year ago

No, that will force string interpretation of the token Overage. MySQL will summarily truncate that to a zero and will issue a warning something like: Truncated incorrect DOUBLE value: 'Overage'

Collectives™ on Stack Overflow

MySQL query select and compute value

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related