title: Using Group By With "Select" Subqueries
body: <p>I'm trying to group my table by weekday, so I can see the count of each payment type along with other sales information split out by weekday. However, because I'm using subqueries, I just get a list of repeating values since the subquery can only return a single value. Is there an easy way to group this by weekday? I think I read elsewhere that I might have to do a series of joins for each subquery, but since there are 8 of them, I'd rather not do that. My current code is below:</p>
  
  <pre><code>select datename(weekday, transaction_date) as [Day],
  (select count(tender_type) from store_visits where tender_type = '0') as [Cash],
  (select count(tender_type) from store_visits where tender_type = '1') as [Check],
  (select count(tender_type) from store_visits where tender_type = '2') as [Gift Card],
  (select count(tender_type) from store_visits where tender_type = '3') as [Discover],
  (select count(tender_type) from store_visits where tender_type = '4') as [Direct Credit],
  (select count(tender_type) from store_visits where tender_type = '5') as [Business Credit],
  (select count(tender_type) from store_visits where tender_type = '6') as [Personal Credit],
  (select count(tender_type) from store_visits where tender_type = '7' or tender_type = '8' or tender_type = '9') as [Other] 
  from store_visits group by datename(weekday, transaction_date)
  </code></pre>