title: Count query in SQL Server
body: <p>I am trying the following to go from X to Y:</p>
  
  <p><strong>tblStationary</strong> ---> X</p>
  
  <pre><code>Name    Stationary
  -------------------
  Peter   Crayon
  Peter   Pencil
  Peter   Crayon
  Peter   Crayon
  Peter   Crayon
  Peter   Pencil
  John    Crayon
  John    Crayon
  </code></pre>
  
  <p>to this query:     ----> Y</p>
  
  <pre><code>Stationary Amount
  -----------------
  Crayon     2
  Pencil     1
  </code></pre>
  
  <p>Crayon and Pencil must only be counted once per person. ie: When Peter has 2 pencils it must be counted as 1. So forth</p>
  
  <p>I tried the following and it does not help: </p>
  
  <pre><code>SELECT 
      Stationary, COUNT(Name) AS Number
  FROM 
      (SELECT * FROM dbo.tblStationary) AS t
  GROUP BY 
      Stationary
  </code></pre>