title: Group Totals from Logs by Month
body: <p>I have a log table that stores media requests by act_datetime, app_id, location_id, media_id and media_type_id. What I want is each resultset row to contain type totals for each month.  For example, log records contain:</p>
  
  <p><a href="https://i.stack.imgur.com/knRiQ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/knRiQ.jpg" alt="enter image description here"></a></p>
  
  <p>I tried using temp tables to extract records by app_id and grouping by month, but I get multiple rows for each total. I can use sub-queries, but how do I get a total row by type for each month?</p>
  
  <p>Any help is greatly appreciated.</p>
  
  <p>Thanks,</p>
  
  <p>Brandon</p>
  
  <p>EDIT</p>
  
  <p>The follow code works combining shared ideas:</p>
  
  <p><a href="https://i.stack.imgur.com/FwGAw.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FwGAw.jpg" alt="enter image description here"></a></p>
  
  <p>This query takes about 13 seconds parsing about 8.1 million rows.  Is that acceptable? Lastly how do you display date as 2018-1 as one column?  I'm getting errors when converting to string since the date is also used in the group and order by clauses.</p>
  
  <p>I also want to try code construct sum( case when media_type_id = 1 then 1 else 0 end )... to see if get same results and speed.</p>
  
  <p>Thanks for everyone's help!</p>