How can I use subquery without WITH?

Tanveer Ahmed 0
Viewed 34
SELECT  Agegroup, Attrition, Attrition / (SELECT Count(Attrition) FROM project1.employee) * 100 as Attrition_rate
from (SELECT Agegroup, Count(*) as Attrition
   FROM(Select
    CASE
        WHEN age BETWEEN 0 AND 20 THEN '<20'
        WHEN age BETWEEN 20 AND 29 THEN '20-29'
        WHEN age BETWEEN 30 AND 39 THEN '30-40'
        WHEN age BETWEEN 40 AND 49 THEN '40-50'
        WHEN age BETWEEN 50 AND 100 THEN 'Greater than 50'
        ELSE 'not specified'
    END AS Agegroup
    FROM project1.employee
    Where Attrition = TRUE) AS T
    GROUP BY Agegroup) AS TT
ORDER BY Agegroup;

This returns zero results, but when I use WITH Clause in BigQuery it works fine. WITH CLAUSE is not working in mySQL workbench

mysql-workbench mysql google-bigquery
1 Answers

Both mysql and BigQuery allow with clauses. Maybe your data is different in these systems? Please provide mySQL version details.

Please consider using windows function with over instead of nested queries. Tested in BiqQuery:

With employee as 
(Select 19 age, true Attrition 
union all select 20,true
union all select 50,true)

SELECT  Agegroup, Attrition, Attrition / sum(Attrition) over ()
from (SELECT Agegroup, Count(*) as Attrition
   FROM(Select
    CASE
        WHEN age BETWEEN 0 AND 20 THEN '<20'
        WHEN age BETWEEN 20 AND 29 THEN '20-29'
        WHEN age BETWEEN 30 AND 39 THEN '30-40'
        WHEN age BETWEEN 40 AND 49 THEN '40-50'
        WHEN age BETWEEN 50 AND 100 THEN 'Greater than 50'
        ELSE 'not specified'
    END AS Agegroup
    FROM  employee
    Where Attrition = TRUE) AS T
    GROUP BY Agegroup) AS TT
ORDER BY Agegroup;
userG7m710
Related