title: SQL Server - Group By, Join issue
body: <p>I have an orders table, which has the following fields:</p>
  
  <p>unid is the primary key field<br>
  order_reference is a system generated incrementing number<br>
  named_worker column will be set to 'Yes' if the order is a named worker order  </p>
  
  <p>I have one order, reference 'BCC-015212' and it's a named worker order.</p>
  
  <p>The following query is using information from the orders table and gives me the expected results:</p>
  
  <pre><code>SELECT 'Order Number'=Case WHEN GROUPING(o.order_reference) = 1 Then 'Grand Total:' ELSE o.order_reference END,
  'Orders Raised' = COUNT(distinct o.unid), 
  'Named Workers' = SUM(CASE o.named_worker WHEN 'Yes' THEN 1 ELSE 0 END)
  FROM orders o
  WHERE o.order_reference = 'BCC-015212'
  Group By o.order_reference
  WITH ROLLUP 
  ORDER BY o.order_reference
  
  Returned Results:
  Order Number | Orders Raised | Named Workers
  Grand Total: | 1             | 1
  BCC-015212   | 1             | 1
  </code></pre>
  
  <p>I'd like to be able to group this information by the agency name which is a column a different table called orderApplicants.</p>
  
  <p>Order 'BCC-015212' has a total of 3 orderApplicants. 1 orderApplicant for Agency1 and 2 for Agency2.</p>
  
  <p>OrderApplicant has a foreign key called order_unid linking to the order table via the unid field.</p>
  
  <p>If I do a join to the OrderApplicants table, the query then gives me inaccurate results on the named worker column:</p>
  
  <pre><code>SELECT 'Order Number'=Case WHEN GROUPING(o.order_reference) = 1 Then 'Grand Total:' ELSE o.order_reference END,
  'Agency'=Case WHEN GROUPING(oa.agency_name) = 1 Then 'Sub Total:' ELSE oa.agency_name END,
  'Orders Raised' = COUNT(distinct o.unid),
  'Named Workers' = SUM(CASE o.named_worker WHEN 'Yes' THEN 1 ELSE 0 END)
  FROM orders o
  Inner Join orderapplicants oa on oa.order_unid = o.unid
  WHERE o.order_reference = 'BCC-015212'
  Group By o.order_reference,oa.agency_name
  WITH ROLLUP 
  ORDER BY o.order_reference, oa.agency_name
  
  Returned Results:
  Order Number | Agency    | Orders Raised | Named Workers
  Grand Total: | Sub Total:| 1             | 3
  BCC-015212   | Sub Total:| 1             | 3
  BCC-015212   | Agency1   | 1             | 1
  BCC-015212   | Agency2   | 1             | 2
  
  Required Results:
  Order Number | Agency    | Orders Raised | Named Workers
  Grand Total: | Sub Total:| 1             | 1
  BCC-015212   | Sub Total:| 1             | 1
  BCC-015212   | Agency1   | 1             | 1
  BCC-015212   | Agency2   | 1             | 1
  </code></pre>
  
  <p>Is there a way for me to group by agency name but give me accurate information from information in the orders table such as named workers?</p>
  
  <p>I've loaded a working example on rextester.com can be found here: <a href="http://rextester.com/SRX8309" rel="nofollow"><a href="http://rextester.com/SRX8309" rel="nofollow">http://rextester.com/SRX8309</a></a></p>