Performing arithmetic operations on derived SQL values

Question

I'm doing some statistics based on a database of states. I would like to output the rank of a state and it's percentage as compared to the other states (i.e. state X's value is higher then 55% of the other states' value).

I'm 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'm getting an SQL error, TotalStates (my derived value) is not found. How can I get all three of these values returned with one query?

Answer 1

You can put the main calculations in a subselect, then reference the aliased columns in the outer query, both to pull the already calculated values and to obtain another one from them:

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

Answer 2

The error you are getting comes from the fact that you are trying to use the derived value in the same select clause that you are creating it in. You will need to maybe do something along 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 efficient or desirable for readability or maintainability. Is there a design reason that you cannot perform this in two queries, or better yet, get the two data items in separate queries and calculate the percentage in the consuming code?