将mysql查询转换为hive

我正在尝试将以下mysql查询转换为hive

MySQL查询

SELECT
    departments.dept_name,
    dept_emp.dept_no,
    gender,
    (count(*)/(select count(*) from employees)) AS Sex
FROM 
    employees,
    dept_emp,departments
WHERE 
    dept_emp.dept_no = departments.dept_no
    AND dept_emp.emp_no =  employees.emp_no
GROUP BY 
    dept_emp.dept_no, 
    departments.dept_name,
    gender
ORDER BY 
    dept_emp.dept_no;

蜂箱查询

WITH 
    q1 as (SELECT COUNT(*) AS TOTAL_COUNT FROM employees),
    q2 as (SELECT gender,COUNT(*) as gender_count FROM employees GROUP BY gender)
SELECT 
    departments.dept_name,
    dept_emp.dept_no,
    gender,
    gender_count/TOTAL_COUNT As Sex 
FROM 
    q1,
    q2,
    dept_emp,
    departments
WHERE 
    dept_emp.dept_no = departments.dept_no
    AND dept_emp.emp_no = dept_emp.emp_no
GROUP BY 
    dept_emp.dept_no, 
    departments.dept_name,
    q2.gender
ORDER BY 
    dept_emp.dept_no;

但是我出错了

SemanticException[错误10025]:行3:53表达式不在 GROUP BY 关键: TOTAL_COUNT

提前谢谢!