title: How to join two sub queries and find the difference of attribute values in SQL
body: <p>From the below query, I want to know how to combine these two sub-queries and also find the difference of values of the attributes specified for two different years.</p>
  
  <p>I had tried joining them using joins, but I do not have a common attribute on which I can combine them as they are for two different years. I need to find the difference in order to compute the change as seen in the attached picture. Can anyone help?</p>
  
  <pre><code>(select (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD,1,4)))-1 as 'Year1',
     SUM(CASE WHEN dh.HOME = 'f' THEN 1 else 0 END) as 'foriegn1',
     SUM(CASE WHEN dh.HOME = 'o' THEN 1 else 0 END) AS 'outofstate1',
     SUM(CASE WHEN dh.HOME = 'i' THEN 1 else 0 END)  AS 'texas1',
     SUM(CASE WHEN dh.HOME = 'u' THEN 1 else 0 END)  AS 'Unknown1',
  
     COUNT(*) as Totalenrollment
     FROM dw_enrollment_F d
     inner join dim_Time t
     on d.TIME_KEY = t.TIME_KEY
     inner join dim_Home dh
     on d.HOME_KEY = dh.HOME_KEY
     inner join dim_Student_Level sl
     on d.STUDENT_LEVEL_KEY = sl.STUDENT_LEVEL_KEY
     where t.ACADEMIC_PERIOD_all =20162
    and t.ACADEMIC_PERIOD_ALL not in('20157','20165','20168','20169','20167')
     GROUP BY (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD,1,4)) ) -1) 
  
  
     (select (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD,1,4)))-1 as 'Year2',
     SUM(CASE WHEN dh.HOME = 'f' THEN 1 else 0 END) as 'foriegn2',
     SUM(CASE WHEN dh.HOME = 'o' THEN 1 else 0 END) AS 'outofstate2',
     SUM(CASE WHEN dh.HOME = 'i' THEN 1 else 0 END)  AS 'texas2',
     SUM(CASE WHEN dh.HOME = 'u' THEN 1 else 0 END)  AS 'Unknown2',
     COUNT(*) as Totalenrollment2
     FROM dw_enrollment_F d
     inner join dim_Time t
     on d.TIME_KEY = t.TIME_KEY
     inner join dim_Home dh
     on d.HOME_KEY = dh.HOME_KEY
     inner join dim_Student_Level sl
     on d.STUDENT_LEVEL_KEY = sl.STUDENT_LEVEL_KEY
     where t.ACADEMIC_PERIOD_all = 20172
    and t.ACADEMIC_PERIOD_ALL not in('20157','20165','20168','20169','20167')
     GROUP BY (CONVERT(INT,SUBSTRING(ACADEMIC_PERIOD,1,4)) ) -1) 
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/4kq1t.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4kq1t.png" alt="enter image description here"></a></p>