MySQL学习-Subquery

以下方法只能解决列是已存在原表中的情形下，在原表基础上填写以某原列的聚合函数值，但是如果在原表基础上添加一列值后，想再添加一列基于新添加列的值得聚合函数，怎么编写呢？

网上例子：

SELECT
gender,
height,
(SELECT
COUNT(*)
FROM
heights
WHERE
a.gender = gender) AS total_of_group
FROM
(SELECT
gender, height
FROM
heights
ORDER BY gender , height) AS a

带解决问题：

SELECT IF(cleaned_users.country=‘US’,‘In US’,
IF(cleaned_users.country=‘N/A’,‘Not Applicable’,‘Outside US’)) AS US_user,
count(cleaned_users.user_guid)
FROM (SELECT DISTINCT user_guid, country
FROM users
WHERE country IS NOT NULL) AS cleaned_users
GROUP BY US_user