How to count consecutively a sequence of number

Question

I need to SELECT and COUNT the amount of OrderIDs which have received a 10% discount.

I've tried using a COUNT function, but it only counts one unique entity occurrence and not for each of the OrderIDs.

USE Northwind
GO

SELECT a.OrderID, COUNT(a.OrderID) as 'SeqNo', b.ProductName, a.UnitPrice, a.Quantity, a.UnitPrice*a.Quantity as Amount, a.Discount
FROM [Order Details] as a
INNER JOIN [Products] as b
ON a.ProductID = b.ProductID
GROUP BY a.OrderID, b.ProductName, a.UnitPrice, a.Quantity, a.Discount
HAVING a.Discount = 0.1

I actually want the 'SeqNo' to COUNT the OrderID but instead all of them are 1.

  OrderID SeqNo       ProductName      UnitPrice Quantity Amount Discount
1  10288  | 1  | Tourtiere             | 5.9    |  10 |   59.00  | 0.1
2  10288  | 2  | Scottish Longbreads   | 10     |  3  |   30.00  | 0.1
3  10291  | 1  | Konbu                 | 4.8    |  20 |   96.00  | 0.1
3  10291  | 2  | Gula Malacca          | 15.5   |  24 |   372.00 | 0.1
3  10291  | 3  | Mankimup Dried Apples | 42.4   |  2  |   84.8   | 0.1

Answer 1

You need to use row_number() function with partition by and order by parts as

row_number() over (partition by OrderID  order by OrderID ) as SeqNo

to start from 1 for each counting of distinct OrderID