title: Group By in sql subquery
body: <p>So i have a query where i am trying to accumulate the count of order for phone and website however i realise you cant use a GROUP BY clause in the subquery when it is directly within a select as it returns more than one value. Therefore my question is where do i put the subquerys to achieve the same result. </p>
  
  <pre><code>SELECT CAST(a.DateCreated AS DATE), 
          (SELECT count(CAST(DateCreated AS DATE))
           FROM [Sterlingbuild].[dbo].[CustomerOrder]
           WHERE BookingSourceId = 1
           GROUP BY CAST(DateCreated AS DATE)) AS 'Website',
           (SELECT count(CAST(DateCreated AS DATE))
           FROM [Sterlingbuild].[dbo].[CustomerOrder]
           WHERE BookingSourceId = 2
           GROUP BY CAST(DateCreated AS DATE)) AS 'Phone'
    , count(CAST(a.DateCreated AS DATE)) AS 'Total Orders'
    FROM [Sterlingbuild].[dbo].[CustomerOrder] a
    WHERE CustomerOrderStatusId = 7
    AND DepartmentId = 1
    GROUP BY CAST(a.DateCreated AS DATE)
    ORDER BY CAST(a.DateCreated AS DATE)
  </code></pre>
  
  <p>When i run this query it works therefore i know the data is correct:</p>
  
  <pre><code>  SELECT count(CAST(DateCreated AS DATE))
       FROM [Sterlingbuild].[dbo].[CustomerOrder]
       WHERE BookingSourceId = 1
       AND CustomerOrderStatusId = 7 AND DepartmentId = 1
       GROUP BY CAST(DateCreated AS DATE)
  </code></pre>