Search code examples
mysqlrow-number

ROW_NUMBER not giving me the correct output

I am having some trouble getting the correct row number in my SQL output. I am using the ROW_NUMBER function then partitioning by the ID and the date but nothing seems to be giving me the expected result.

SELECT 
    BCG.BudgetId
    ,CAST(BCG.StartOfPeriod AS DATE) AS StartOfPeriod
    ,ROUND(SUM(BCG.Charge),2) AS ExpenditureBudget
    ,ROUND(SUM(BCG.Consumption),2) AS ConsumptionBudget
    ,ROW_NUMBER() OVER (PARTITION BY BCG.BudgetId,BCG.StartOfPeriod ORDER BY BCG.StartOfPeriod ASC) AS rowNum
FROM B_BudgetCalGroup BCG 
WHERE BCG.BudgetId = 4940
GROUP BY BCG.BudgetId,StartOfPeriod

Current Output:

4940    2021-08-01  281495.36   2962365.8   1
4940    2021-09-01  276476.49   2943250.2   1
4940    2021-10-01  303252.47   3143659.7   1
4940    2021-11-01  292298.37   3090468.5   1
4940    2021-12-01  268543.23   2824347.8   1

Expected Output:

4940    2021-08-01  281495.36   2962365.8   1
4940    2021-09-01  276476.49   2943250.2   2
4940    2021-10-01  303252.47   3143659.7   3
4940    2021-11-01  292298.37   3090468.5   4
4940    2021-12-01  268543.23   2824347.8   5

Am I doing something wrong?

Solution

  • This happens because of:

     PARTITION BY BCG.BudgetId,BCG.StartOfPeriod

    This will reset the row number whenever the StartOfPeriod is different, which is your case. So replace with:

     PARTITION BY BCG.BudgetId

    Hint: there should never be a reason to have the same field appear in the PARTITION BY and ORDER BY part of an OVER clause.