Combine two queries to get the data in two columns

SELECT 
    tblEmployeeMaster.TeamName, SUM(tblData.Quantity) AS 'TotalQuantity'
FROM 
    tblData 
INNER JOIN
    tblEmployeeMaster ON tblData.EntryByHQCode = tblEmployeeMaster.E_HQCode 
INNER JOIN
    tblPhotos ON tblEmployeeMaster.TeamNo = tblPhotos.TeamNo
WHERE
    IsPSR = 'Y'
GROUP BY 
    tblPhotos.TeamSort, tblPhotos.TeamNo, tblPhotos.Data, 
    tblEmployeeMaster.TeamName
ORDER BY
    tblPhotos.TeamSort DESC, TotalQuantity DESC

This returns

Using this statement

select TeamName, count(TeamName) AS 'Head Count' 
from dbo.tblEmployeeMaster
where IsPSR = 'Y'
group by teamname

Which returns

I would like to combine these 2 queries in 1 to get the below result.

Tried union / union all but no success 🙁

Any help will be very much helpful.

Answer

You can simply use the sub-query as follows:

SELECT tblEmployeeMaster.TeamName, SUM(tblData.Quantity) AS 'TotalQuantity', 
        MAX(HEAD_COUNT) AS HEAD_COUNT, -- USE THIS VALUE FROM SUB-QUERY
        CASE WHEN MAX(HEAD_COUNT) <> 0 
             THEN SUM(tblData.Quantity)/MAX(HEAD_COUNT) 
        END AS PER_MAN_CONTRIBUTION -- column asked in comment
  FROM tblData INNER JOIN
  tblEmployeeMaster ON tblData.EntryByHQCode = tblEmployeeMaster.E_HQCode INNER JOIN
  tblPhotos ON tblEmployeeMaster.TeamNo = tblPhotos.TeamNo
  -- FOLLOWING SUB-QUERY CAN BE USED
  LEFT JOIN (select TeamName, count(TeamName) AS HEAD_COUNT 
                from dbo.tblEmployeeMaster
               where IsPSR = 'Y' group by teamname) AS HC  
         ON HC.TeamName = tblEmployeeMaster.TeamName
  where IsPSR = 'Y'
  GROUP BY tblPhotos.TeamSort, tblPhotos.TeamNo, tblPhotos.Data,tblEmployeeMaster.TeamName
  order by tblPhotos.TeamSort desc, TotalQuantity desc