0

I have this SQL select and I don't know how can I use 'total' to do the division.

select count(*) as total
from table
where id = 15/total;

I get this error:

ORA-00904: "TOTAL": invalid identifier

jarlh
  • 42,561
  • 8
  • 45
  • 63
ad3luta
  • 19
  • 4
  • Please share more details, like the database system you are using – Nico Haase Dec 21 '20 at 19:26
  • Does https://stackoverflow.com/questions/27503379/use-a-calculated-column-in-a-where-clause help? – Nico Haase Dec 21 '20 at 19:26
  • I'm using oracle database. Or how can I use the 'total' in a select such as - select count(*) as total, partialNumber:=15/total as partial from table where id = 15; – ad3luta Dec 21 '20 at 19:28
  • Comparing `id` to the result of arithmetic doesn't really make sense to me. Can you provide sample data, desired results, and an explanation of what you want to accomplish? – Gordon Linoff Dec 21 '20 at 19:34
  • I want to get the percentages from a group of ages..eg - there are 33% people which have age between 40 and 45, 50% with age between 20-35 etc.. Can't do the division bcs 'total' is invalid - count(*)*100/total SELECT (select count(*) from table where id=5) as total, count (*)*100/total as Procents FROM tabel group by id; – ad3luta Dec 21 '20 at 19:41

2 Answers2

3

use a subquery and analytic function

select * from
(
select id, count(*) over() as total 
  from table 
) s
where id=15/total

UPDATE based on your comment - finding percent for each age group:

select age_group, count(*)*100/total as pct
from
(
select   
      case when age between 40 and 45 then 'group_45'           
           when age between 20 and 30 then 'group_30'
             ... add more groups
           else 'other'
      end as age_group, 
      count(*) over() as total
)s
group by age_group
leftjoin
  • 36,950
  • 8
  • 57
  • 116
0

jarih is right you can't use an alias like that, your one alternative is:

WITH TOTALS AS (
    SELECT COUNT(*) AS TOTAL FROM TABLE
) SELECT COUNT(*) FROM TABLE CROSS JOIN TOTALS
WHERE TABLE.ID = 15/TOTAL;
E. Mergen
  • 1
  • 1