title: COUNT number of rows in a GROUP on higher aggregate level
body: <p>I am trying to find out how many rows of a certain item exist in the table, e.g. in the following example for itemID 1 I need the result 5 (not 3, which is what I currently get). I am tempted to add TransactionID into the PARTITION BY clause, but that results in Msg 8120 since the query does not GROUP by TransactionID. Well, if it did then getting that count would be easy, but I do not want to group on Transaction Level. What can I do to get that ItemCount right? It must be so easy but I am banging my head.</p>
  
  <pre><code>DECLARE @t TABLE (TransactionID INT PRIMARY KEY IDENTITY, CustomerID INT, ItemID INT);
  
  INSERT INTO @t (CustomerID, ItemID)
  VALUES
      (1, 1),
      (2, 1),
      (3, 1),
      (4, 2),
      (1, 1),
      (2, 2),
      (3, 3),
      (4, 4),
      (1, 1);
  
  SELECT
      CustomerID,
      ItemID,
      Rows = COUNT(*),
      ItemRowCount = COUNT(*) OVER (PARTITION BY ItemID)
  FROM
      @t
  GROUP BY
      CustomerID,
      ItemID
  ORDER BY
      ItemID,
      CustomerID;
  </code></pre>
  
  <p>EDIT: I was overaggregating, I guess. Sebastian Meine got me on the track and his answer is right so I accepted it. However, this subquery works for my:</p>
  
  <pre><code>SELECT
      CustomerID,
      ItemID,
      Rows = COUNT(*),
      ItemRowCount = (SELECT COUNT(*) FROM @t x WHERE t.ItemID = x.ItemID)
  FROM
      @t t
  GROUP BY
      CustomerID,
      ItemID
  ORDER BY
      ItemID,
      CustomerID;
  </code></pre>