Search code examples
mysqlsqlauto-incrementrowid

mysql row number is not working properly with order

I have a Store Database with the following tables :

1 - Provider(ProviderID,Name,Country)

2 - Product(ProductID,ProviderID,ProductPrice)

3 - Command(CommandID,ProductID,ProductQuantity,CommandDate)

I made a query that counts the gain by year

SELECT EXTRACT(YEAR FROM COMMAND_DATE) AS year, 
SUM(PRODUCT_PRICE*PRODUCT_QUANTITY) AS gain
FROM PRODUCT JOIN COMMAND ON PRODUCT.PRODUCT_ID=COMMAND.PRODUCT_ID
GROUP BY year
ORDER BY year

This is the output :

enter image description here

Now I want to display the row number just like Oracle, so I used this query : 

SET @currentRow = 0;
SELECT @currentRow := @currentRow + 1 AS counter,
EXTRACT(YEAR FROM COMMAND_DATE) AS year,
SUM(PRODUCT_PRICE*PRODUCT_QUANTITY) AS gain
FROM PRODUCT JOIN COMMAND ON PRODUCT.PRODUCT_ID=COMMAND.PRODUCT_ID
GROUP BY year
ORDER BY year

But I don't get what I want

enter image description here

It seems that order is affecting the row number. I want it to start from 1. Ordering by counter is not an option because I need to order by years.

This is how I want it to be :

1 2014 1863
2 2015 889
3 2016 2626
...
Solution

  • With group by, you need a subquery:

    SELECT (@currentRow := @currentRow + 1) AS counter, y.*
FROM (SELECT EXTRACT(YEAR FROM COMMAND_DATE) AS year,
             SUM(PRODUCT_PRICE*PRODUCT_QUANTITY) AS gain
      FROM PRODUCT JOIN
           COMMAND
           ON PRODUCT.PRODUCT_ID = COMMAND.PRODUCT_ID
      GROUP BY year
      ORDER BY year
     ) y CROSS JOIN
     (SELECT @currentRow := 0) params;

    Or, you can use ROW_NUMBER() OVER (ORDER BY YEAR), if you are using MySQL 8+.