Search code examples
sqlitegroup-bysubquerypercentagecase-when

2 result perecentage with the same table

select AGE,
(count (case when age > 22 then name end)*100.0/count(name)) as percentage
from student;

Result 20 30.0

select name, age,
(select 100*age/sum(age) over () as percentage from student) as percentage ,
(count(case when age > 22 then name end )*100.0/ count (name)) as Total_Perecentage 
from student
WHERE age > 22
GROUP BY 1,2;

Result

Elis    27  9   100.0
Mice    23  9   100.0
Sara    25  9   100.0

My question How can I update the Total_percentage from 100% to 30% ( all result in one table ) as the first query

In simple words, I need to say the 30% is the total percentage of 3 names who above 22 of all 10 students.

Result

Solution

  • It's easier with a subquery that aggregates conditionally on the table:

    SELECT name, age, 
       (SELECT 100 * AVG(age > 22) FROM student) AS Total_Perecentage 
FROM student 
WHERE age > 22

    See the demo.
    Results:

    name age Total_Perecentage
    Sara 25 30
    Mice 23 30
    Elis 27 30