Geek Answers Handbook

Performing Arithmetic with Derived SQL Values

I am doing statistics based on a state database. I would like to derive the state rank and its percentage compared to other states (that is, the value of state X is above 55% of the value of other states).

I am trying something like this:

SELECT count(*) AS TotalStates, (SELECT COUNT(*) FROM states) AS NumberStates, (TotalStates/NumStates) AS percentage FROM states WHERE CRITERIA > 7.5

I get an SQL error, TotalStates (my derived value) was not found. How can I get all three values ​​returned by a single query?

+7
source share
2 answers

You can put the main calculations in a subquery, then refer to the columns with an alias in the external query, both to extract the already calculated values ​​and to get another one:

 SELECT TotalStates, NumberStates, TotalStates / NumberStates AS percentage FROM ( SELECT COUNT(*) AS TotalStates, (SELECT COUNT(*) FROM states) AS NumberStates FROM states WHERE CRITERIA > 7.5 ) s
+7
source

The error you get comes from the fact that you are trying to use the derived value in the same select clause in which you create it. You will need to do something on these lines:

 SELECT count(*) as TotalStates, (SELECT count(*) from states) as NumberStates, (count(*)/(SELECT count(*) from states)) as percentage FROM states WHERE criteria = x

However, this is not very effective or desirable for readability or maintenance. Is there a design reason that you cannot do this in two queries or, even better, get two data elements in separate queries and calculate the percentage in the consuming code?

+2
source

More articles:


All Articles