CODEWITHSUNDEEP
sqlsql-servert-sql
Techno04335
Techno04335

Reputation: 1445

How to join two select count statements

For example If I have a table 'studentActivities' in which every time a student does an activity it is recorded. Like so:

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

I am trying to count the count distinct activities a student does based on if it is In-School Actvity or Out-of-School Activity.

This is distinguished by the 4 In-School Activities via: School Swimming, School Running, School Soccer, School Tennis

What I would like to get is a table as so:

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

I have tried:

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

But this does not give me the result I want. How can I get the result I want?

Upvotes: 2

Views: 134

Answers (8)

Aparna
Aparna

Reputation: 286

The below is simple and works :

select name,countryofbirth,sum(schoolflag) as 
In_School_Activity,sum(nonschool) Out_Of_School_Activity
from 
 (
 select activity,name,countryofbirth,
 case when activity like 'School%' then 1 else 0 end as schoolflag ,
 case when activity not like 'School%' then 1 else 0 end as nonschool
 from schoolactivity
 )a
group by name,countryofbirth

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

Using conditional aggregation with 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 demo: http://rextester.com/EMUWU73595

returns: (After correcting Bob's Country of Birth in the sample data)

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

Upvotes: 1

DataDad
DataDad

Reputation: 21

Bob has 2 countryofbirth locations. Is that 2 separate Bobs or is it an error? I didn't test this so it might not be 100% accurate, but I prefer a common table expression:

;WITH cte
AS (
SELECT DISTINCT NAME AS [student_name]
    ,countryofbirth
    ,activity
    ,CASE 
        WHEN activity IN (
                'School Swimming'
                ,'School Running'
                ,'School Soccer'
                ,'School Tennis'
                )
            THEN 1
        ELSE 0
        END AS [In-school_activity_count]
    ,CASE 
        WHEN activity IS NOT NULL
            AND activity NOT IN (
                'School Swimming'
                ,'School Running'
                ,'School Soccer'
                ,'School Tennis'
                )
            THEN 1
        ELSE 0
        END AS [Out-of-school_activity_count]
FROM studentActivities
)
SELECT student_name
,countryofbirth
,SUM([In-school_activity_count]) AS [In-School_Activities]
,SUM([Out-of-school_activity_count]) AS [Out_of_School_Activities]
FROM cte
GROUP BY student_name
,countryofbirth

Upvotes: 1

Eli
Eli

Reputation: 2608

A Pivot would solve this. I tested it with the data which you provided, and it worked as expected.

SELECT
    Name
    ,countryofbirth
    , [In-School Activity]
    ,[Out-of-School Activity]
FROM(
    SELECT
        name
        ,countryofbirth
        ,CASE Activity
            WHEN 'School Swimming' THEN 'In-School Activity'
            WHEN 'School Running'  THEN 'In-School Activity'
            WHEN 'School Soccer'   THEN 'In-School Activity'
            WHEN 'School Tennis'   THEN 'In-School Activity'        
            ELSE 'Out-of-School Activity'
        END class
    FROM studentactivities
    WHERE Activity IS NOT NULL
)AS base
PIVOT(
    COUNT (CLASS)
    FOR CLASS IN ([In-School Activity],[Out-of-School Activity])
)AS Pivoted

Upvotes: 1

noonex
noonex

Reputation: 2075

This what comes to my mind, I didn't verify, but this is how I got the task

SELECT 
 name AS [student_name],
 countryofbirth AS [Manufacturer],
 sum (activity IN ('School Swimming', 'School Running', 'School Soccer', 'School Tennis')) as [In-School Activities],
 sum (activity NOT IN ('School Swimming', 'School Running', 'School Soccer', 'School Tennis')) as [Out_of_School Activities]
 FROM (select distinct studentActivities.name, studentActivities.countryofbirth, activity) x 
 GROUP BY  name, countryofbirth;

Upvotes: 1

HLGEM
HLGEM

Reputation: 96572

I would use the Case statement:

SELECT 
 Sa.name AS [student_name],
 Sa.countryofbirth,

 Sum(Case when sa.activity IN ('School Swimming', 'School Running', 'School Soccer', 'School Tennis')
 then 1 else 0 end) as In_school_Activities,

 Sum(Case when sa.activity IN ('School Swimming', 'School Running', 'School Soccer', 'School Tennis')
 then 0 else 1 end) as Out_of_school_Activities


 FROM (select distinct student_name, country _of_birth, activity from studentActivities) sa

 GROUP BY  sa.name, sa.countryofbirth

Note that in any non-trivial application, name can never be considered unique. If you have an identifier (and you should anytime you have Name), then it is better to group on that.

Upvotes: 1

DVT
DVT

Reputation: 3127

You should use Full Outer Join

 WITH InSchool AS (
 SELECT 
 studentActivities.name AS [student_name],
 studentActivities.countryofbirth ,
 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
 )
, OutOfSchool AS ( 
 SELECT 
 studentActivities.name AS [student_name],
 studentActivities.countryofbirth,
 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
 )
 SELECT
  COALESCE(InSchool.student_name, OutOfSchool.student_name) [Student_Name],
  COALESCE(InSchool.CountryOfBirth, OutOfSchool.CountryOfBirth) CountryOfBirth,
  COALESCE(InSchool.[In-School Activities],0) [In-School Activities],
  COALESCE(OutOfSchool.[Out_of_School Activities],0) [Out_of_School Activities]
 FROM InSchool
 FULL OUTER JOIN OutOfSchool
 ON InSchool.[student_name] = OutOfSchool.[Student_name]
 AND InSchool.CountryOfBirth = OutOfSchool.CountryOfBirth

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

You should use a join (and not an union )

select a.[student_name], a.[student_name],  a.[In-School Activities], b.[Out_of_School Activities]
from ( 
  SELECT 
   studentActivities.name AS [student_name],
   studentActivities.countryofbirth AS [Manufacturer],
   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 
 ) a
 left  join (
   SELECT 
   studentActivities.name AS [student_name],
   studentActivities.countryofbirth AS [Manufacturer],
   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 
 ) b on a.[student_name] = b.[student_name] and a.[Manufacturer] = b.[Manufacturer]

Upvotes: 1

Related Questions