How to not include calculations in the SQL queries so as to not violate the mathematical order of operations (PEDMAS)?

Question

I wanted to know if there is another way of handling mathematical calculations in SQL queries so as to optimize the queries and avoid violating the mathematical order of calculations (PEDMAS)

Below is an example-

SELECT
e.area_code, 
e.A, 
e.B, 
e.B/e.A as Rate, 
e.C, 
e.D, 
e.profit
e.value
FROM
(
Select 
SUM(CASE WHEN type ='stop' THEN 1 ELSE 0 END) A, 
SUM (Case WHEN type = 'start' THEN 1 ELSE 0 END) B,
SUM(C) C, SUM(D) D, 
(SUM(C) - SUM(D)) AS profit, 
(Z-(B/A)/(D/C)) value,
CASE
WHEN SUBSTR(area_code, 1, 3) IN ("469") THEN "TX"
WHEN SUBSTR(area_code, 1, 3) IN ("551") THEN "CA"
WHEN SUBSTR(area_code, 1, 3) IN ("973") THEN "NY"
ELSE "other"
END AS area_code
FROM db.table1
GROUP BY area_code
) AS e

Is there any other efficient way to handle this?

Answer 1

You cannot use aliases (A, B, ...) in other expressions in the same SELECT (B/A), ...

You half-way avoided that bug by having the nested SELECTs. But you need to go all the way.

Also, please don't say SUM(c) c -- Humans and computers get confused as to which c is which.

This:

SUM(CASE WHEN type ='stop' THEN 1 ELSE 0 END) A, 

can be shortened to

SUM(type ='stop') A, 

and

WHEN SUBSTR(area_code, 1, 3) IN ("551") THEN "CA"

to

WHEN area_code LIKE '551%' THEN 'CA'