CODEWITHSUNDEEP
sqlpostgresql
Kevin
Kevin

Reputation: 8207

Filter a select where results of expression meet criteria

Rarely interact with db's and no formal training. I'm trying to select the result.id that meet a calculated criteria after an INNER JOIN.

results table:

|  id  |  infos_id  |  ...
 00001    99999
 00002    99998
 ...

infos table:

|  info_id  |  counts_id  |  ...
    99999      12345
    99998      12346
    ...

counts table:

|  count_id  |  numerator  |  denom  |  ...
   12345          100         1000
   12346          150         1000
   ...

When I perform this query:

SELECT r.id AS result_id, 
c.count_id AS count_id,
c.numerator/CAST(c.denom AS numeric) * 100 AS freq 
FROM results r INNER JOIN infos i ON r.infos_id = i.info_id 
INNER JOIN counts c ON i.counts_id = c.count_id 
WHERE r.id = 00001;

I get these results:

 result_id | count_id |          freq          
----------+-----------+------------------------
 00001     |   12345  | 10.0

I'd like do this for all N results and filter by freq, when I try substituting the WHERE clause with this:

WHERE freq < 12.0;

I get this:

ERROR:  column "freq" does not exist

Is there a simple change I can make to have my query return only the results.id that are < some given freq?

Upvotes: 0

Views: 38

Answers (1)

Wyatt Shipman
Wyatt Shipman

Reputation: 1789

You will need to use the calculation in the WHERE statement instead of the alias of freq. Something like below should work:

SELECT r.id AS result_id, 
c.count_id AS count_id,
c.numerator/CAST(c.denom AS numeric) * 100 AS freq 
FROM results r 
INNER JOIN infos i 
    ON r.infos_id = i.info_id 
INNER JOIN counts c 
    ON i.counts_id = c.count_id 
WHERE c.numerator/CAST(c.denom AS numeric) * 100 < 12

Upvotes: 1

Related Questions