title: window function count aggregation
body: <p>I have two quite complex queries going on here. Both should return per given country (Netherlands in this case) the monthly use over the total of 12 months and the percentage that makes up that month of the total of those 12 months.</p>
  
  <p>But the one where i use count as a windowing function returns one more row than the one where i don't use the count as a windowing function. </p>
  
  <p><a href="https://i.stack.imgur.com/hqcUC.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hqcUC.jpg" alt="enter image description here"></a></p>
  
  <p>The query for the left side picture is: </p>
  
  <pre><code>;WITH MonthUsage AS
  (
    SELECT customer_id, country_name, [Month], [Year], SUM(ItemsPerMonth) 
  AS ItemsPerMonth
    FROM (
      SELECT cs.country_name, c.customer_id, YEAR(mol.date_watched) AS Year, MONTH(mol.date_watched) AS [Month], COUNT(*) AS ItemsPerMonth
      FROM Customer c
      JOIN Customer_Subscription CS ON C.customer_id = CS.customer_id
      JOIN Movie_Order_Line mol ON mol.customer_id = c.customer_id
      WHERE (mol.date_watched BETWEEN '2017-07-01' AND '2018-07-01') AND cs.country_name = 'Nederland'
      GROUP BY c.customer_id,cs.country_name, YEAR(mol.date_watched), 
  MONTH(mol.date_watched)
  
  UNION ALL
  
  SELECT cs.country_name, c.customer_id, YEAR(sol.date_watched) AS Year, 
  MONTH(sol.date_watched), COUNT(*) AS ItemsPerMonth
        FROM Customer c
        JOIN Customer_Subscription CS ON C.customer_id = CS.customer_id
        JOIN Show_Order_Line sol ON sol.customer_id = c.customer_id
        WHERE sol.date_watched BETWEEN '2017-07-01' AND '2018-07-01'
        GROUP BY c.customer_id, cs.country_name, YEAR(sol.date_watched), 
      MONTH(sol.date_watched)
  ) AS MonthItems
  WHERE country_name = 'Nederland'
  GROUP BY customer_id, country_name, [Month], [Year]
  ),
  Months(MonthNumber) AS 
  (
    SELECT 1
    UNION ALL
    SELECT MonthNumber + 1 
    FROM months
    WHERE MonthNumber &lt; 12
  )
  SELECT cmb.[Year], ISNULL(cmb.[Month], m.MonthNumber) AS [Month], 
  ISNULL(ItemsPerMonth, 0) AS ItemsPerMonth, 
  ISNULL(FORMAT(((CAST(ItemsPerMonth AS decimal) / CAST((
      SELECT SUM(ItemsPerMonth) 
      FROM MonthUsage) AS decimal
  ))),'P0'), '0%') AS [PercentageOfTotal]
  FROM MonthUsage cmb
  JOIN Customer c ON c.customer_id = cmb.customer_id
  JOIN Months m ON m.MonthNumber = cmb.[Month]
  ORDER BY cmb.[Year] ASC, [Month] ASC
  </code></pre>
  
  <p>And the query for the right picture is: </p>
  
  <pre><code>;WITH MonthUsage AS (
  SELECT *
  FROM (
      SELECT 
          YEAR(date_watched) AS [Year], 
          MONTH(date_watched) AS [Month], 
          COUNT(order_id) OVER(PARTITION BY CONCAT(YEAR(date_watched), MONTH(date_watched))) AS ItemsPerMonth
      FROM Movie_Order_Line mov
      JOIN Customer c ON c.customer_id = mov.customer_id
      JOIN Customer_Subscription cs ON C.customer_id = cs.customer_id
      WHERE date_watched BETWEEN '2017-07-01' AND '2018-07-01' AND cs.country_name = 'Nederland'
  
      UNION ALL
  
      SELECT YEAR(date_watched) AS [Year], MONTH(date_watched) AS [Month], COUNT(order_id) OVER(PARTITION BY CONCAT(YEAR(date_watched), MONTH(date_watched))) AS ItemsPerMonth
      FROM Show_Order_Line sol
      JOIN Customer c ON c.customer_id = sol.customer_id
      JOIN Customer_Subscription cs ON C.customer_id = cs.customer_id
      WHERE date_watched BETWEEN '2017-07-01' AND '2018-07-01' AND cs.country_name = 'Nederland'
  ) AS Combined
  GROUP BY [YEAR], [Month], ItemsPerMonth
  )
  SELECT *, ISNULL(FORMAT(((CAST(ItemsPerMonth AS decimal) / CAST((SELECT 
  SUM(ItemsPerMonth) FROM MonthUsage) AS decimal))),'P0'), '0%') AS 
  [PercentageOfTotal]
  FROM MonthUsage
  ORDER BY [Year] ASC, [Month] ASC
  </code></pre>
  
  <p>I can't seem to figure out why i'm getting different results. Any help is much appreciated. Thank you in advance for your time. </p>