title: Filter rows by count of two column values in SQL
body: <p>In SQL Server, I have the following table:</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE x
  (
      ColumnA CHAR NOT NULL
    , ColumnB CHAR NOT NULL
    , ColumnC INT NOT NULL
    , CONSTRAINT CompositeKey PRIMARY KEY (ColumnA, ColumnB)
  );
  </code></pre>
  <p>With the following data:</p>
  <pre><code>INSERT INTO x
  VALUES
   ('A', 'X', 2)
  ,('A', 'Y', 1)
  ,('B', 'X', 4)
  ,('B', 'Z', 2)
  ,('C', 'X', 9)
  ,('C', 'Y', 3)
  ,('C', 'P', 2)
  ,('D', 'X', 6)
  ,('D', 'Y', 4)
  ,('E', 'P', 2)
  </code></pre>
  <p>I am writing a query with the following 2 predicates:</p>
  <ol>
  <li><code>Count(ColumnA) &gt; 1</code></li>
  <li><code>Count(ColumnB) &gt; 2</code></li>
  </ol>
  <p><strong>MY QUERY</strong></p>
  <pre><code>SELECT *
  FROM
  (
      SELECT *
            ,COUNT(*) OVER (PARTITION BY [ColumnA]) AS columnA_cnt
            ,COUNT(*) OVER (PARTITION BY [ColumnB]) AS columnB_cnt
      FROM @DataSource
  ) DS
  WHERE columnA_cnt &gt;= 2
    AND columnB_cnt &gt;= 3
  </code></pre>
  <p><strong>EXPECTED RESULT</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>A</td>
  <td>X</td>
  <td>2</td>
  </tr>
  <tr>
  <td>A</td>
  <td>Y</td>
  <td>1</td>
  </tr>
  <tr>
  <td>C</td>
  <td>X</td>
  <td>9</td>
  </tr>
  <tr>
  <td>C</td>
  <td>Y</td>
  <td>3</td>
  </tr>
  <tr>
  <td>D</td>
  <td>X</td>
  <td>6</td>
  </tr>
  <tr>
  <td>D</td>
  <td>Y</td>
  <td>4</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>ACTUAL RESULT</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>A</td>
  <td>X</td>
  <td>2</td>
  </tr>
  <tr>
  <td>A</td>
  <td>Y</td>
  <td>1</td>
  </tr>
  <tr>
  <td>B</td>
  <td>X</td>
  <td>4</td>
  </tr>
  <tr>
  <td>C</td>
  <td>X</td>
  <td>9</td>
  </tr>
  <tr>
  <td>C</td>
  <td>Y</td>
  <td>3</td>
  </tr>
  <tr>
  <td>D</td>
  <td>X</td>
  <td>6</td>
  </tr>
  <tr>
  <td>D</td>
  <td>Y</td>
  <td>4</td>
  </tr>
  </tbody>
  </table>
  </div>