title: Grouping on two columns and counting distinct values using R
body: <p>Using R, I am trying to get two aggregate function max and count distinct and for the values present in a data frame and group them based on two other columns.  </p>
  
  <pre><code>A = c('A', 'A', 'A', 'B', 'B', 'B')
  B = c('R1', 'R2', 'R1', 'R1', 'R2', 'R1')
  C = c(11, 2, 8, 11, 11, 17)
  d = data.frame (A, B, C)
  
  sqldf('select A, B, count(distinct(C)) as UNIQUE_COUNT from d group by A, B')
  </code></pre>
  
  <p>Correct output</p>
  
  <pre><code>  A  B UNIQUE_COUNT
  1 A R1            2
  2 A R2            1
  3 B R1            2
  4 B R2            1
  </code></pre>
  
  <p>I am able to do that using normal sql using the sqldf package in R. I was trying to do the same using native R to avoid sql. I tried the following query given below but it gave a wrong output.</p>
  
  <p>Wrong query</p>
  
  <pre><code>d %&gt;%
  group_by(A,B)%&gt;%
  summarise(UNIQUE_COUNT = n_distinct(C))
  </code></pre>
  
  <p>Wrong output</p>
  
  <pre><code>  UNIQUE_COUNT
  1                4
  </code></pre>
  
  <p>Any suggestion on what I am doing wrong?</p>