Q:

Group by Returns Multiple Rows

My query below returns multiple lines instead of aggregating the count. What am I doing wrong.
select case when s.Customer is not null then 'Paid' else 'Free' END as Paid_Free,
       Status,case when CompleteDate < '4/1/2018' then '1H' else '2H' end as Busy, 
       count(*) userCount
from CompletesCur OS 
   left outer join  Sales s on S.Cust = ID
WHERE s.ReturnYear = 2017
  and s.MediaType = 'Online'
  and os.ReturnYearFiled = 2017
  and s.ReturnYear = s.TransactionTaxYear2
  and s.ProductGroup in ('Fed','State','Phone Support', 'Import')
GROUP by s.Customer,Status,CompleteDate

Results:
Paid_Free   Status          Busy   Usercount
Paid      Returning Yr3+     2H      2
Paid      Returning Yr3+     2H      1
Paid      Returning Yr3+     1H      1
Paid      Returning Yr3+     1H      1
Paid      Returning Yr2+     2H      2
Paid      Returning Yr2+     2H      2

Results Requested:
Paid_Free    Status            Busy    UserCount
Paid        Returning Yr3+      2H       3
Paid        Returning Yr3+      1H       2
Paid        Returning Yr2+      2H       4  

A:

you can try like below
with cte
(
select case when s.Customer is not null then 'Paid' else 'Free' END as Paid_Free,
Status,case when CompleteDate < '4/1/2018' then '1H' else '2H' end as Busy, 
count(*) userCount
     from CompletesCur OS 
   left outer join  Sales s on S.Cust = ID
                  WHERE s.ReturnYear = '2017'
                  and MediaType = 'Online'
                  and os.ReturnYearFiled = 2017
                  and s.ReturnYear = s.TransactionTaxYear2
                  and s.ProductGroup in ('Fed','State','Phone Support', 'Import')

                  GROUP by s.Customer,Status,CompleteDate
) select Paid_Free,Status,Busy, sum(userCount) from cte
  group by Paid_Free,Busy,Status

