title: count transaction per day for each customer
body: <p>How can I count transaction for each customer per day?</p>
  
  <p>I have this table</p>
  
  <pre><code>badge_id    trans_date
  badge1      2017-08-03 10:36:54.107
  badge2      2017-08-07 10:39:00.550
  badge2      2017-08-07 17:39:00.550
  badge1      2017-08-07 12:31:28.467
  badge1      2017-08-07 12:33:12.180
  badge2      2017-08-08 12:46:02.250
  </code></pre>
  
  <p>the result should be like this</p>
  
  <pre><code>badge_id    trans_date  counter
  badge1      2017-08-03      1
  badge2      2017-08-07      2
  badge1      2017-08-07      2
  badge2      2017-08-08      1
  </code></pre>
  
  <p>the problem is when I use this query</p>
  
  <pre><code>SELECT CAST([trans_date] as DATE), 
  COUNT(badge_id) AS counter 
  FROM trans_details 
  GROUP BY trans_date
  </code></pre>
  
  <p>It's just showing every date with 1 count</p>
  
  <p>when I change to <code>GROUP BY badge_id</code> It showing this error</p>
  
  <p>Msg 8120, Level 16, State 1, Line 1
  Column 'trans_details.trans_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</p>
  
  <p>what should i do? Thank you..</p>