sql - 如何连接两个 select count 语句

Question

例如，如果我有一个表'studentActivities'，其中记录了学生每次进行的事件。像这样:

activity         countryofbirth    name    event_date
School Swimming  USA               Bob     5/21/2017 12:50
Park Swimming    Australia         Sarah   2/11/2017 19:50
Park Swimming    Australia         Sarah   2/13/2017 16:50
Park Running     USA               Bob     2/10/2017 11:50
School Tennis    USA               Bob     2/12/2017 11:50
NULL             USA               Jane    8/4/2016 13:30

我正在尝试根据学生是在校事件还是校外事件来计算学生进行的不同事件的数量。

4 校内事件的特点是:学校游泳、学校运行、学校足球、学校网球

我想得到的是这样的表格:

 Student_Name Country_of_Birth In-School_Activities Out_of_School_Activities
 Bob          USA              2                    1
 Sarah        Australia        0                    1
 Jane         USA              0                    0

我试过:

SELECT 
 studentActivities.name AS [student_name],
 studentActivities.countryofbirth AS County_of_Birth],
 COUNT (DISTINCT activity) as [In-School Activities]

 FROM studentActivities

 WHERE studentActivities.activity IN ('School Swimming', 'School Running', 'School Soccer', 'School Tennis')

 GROUP BY  studentActivities.name, studentActivities.countryofbirth

 UNION

 SELECT 
 studentActivities.name AS [student_name],
 studentActivities.countryofbirth AS [County_of_Birth],
 COUNT (DISTINCT activity) as [Out_of_School Activities]

 FROM studentActivities

 WHERE studentActivities.activity NOT IN ('School Swimming', 'School Running', 'School Soccer', 'School Tennis')

 GROUP BY  studentActivities.name, studentActivities.countryofbirth

但这并没有给我想要的结果。我怎样才能得到我想要的结果？

Answer 1

通过 count(distinct ...) 使用条件聚合

select 
    Name
  , CountryOfBirth
  , InSchoolActivies    = count(distinct case when activity in ('School Swimming', 'School Running', 'School Soccer', 'School Tennis') then activity end)
  , OutOfSchoolActivies = count(distinct case when activity not in ('School Swimming', 'School Running', 'School Soccer', 'School Tennis') then activity end)
from StudentActivites
group by Name, CountryOfBirth

rextester 演示:http://rextester.com/EMUWU73595

返回:(在更正样本数据中 Bob 的出生国家之后)

+-------+----------------+------------------+---------------------+
| Name  | CountryOfBirth | InSchoolActivies | OutOfSchoolActivies |
+-------+----------------+------------------+---------------------+
| Bob   | USA            |                2 |                   1 |
| Jane  | USA            |                0 |                   0 |
| Sarah | Australia      |                0 |                   1 |
+-------+----------------+------------------+---------------------+