How to find all orders with 10% discount?

Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor or Query Analyzer to get a report on all orders with 10% (0.1) discount. The queries demonstrate INNER JOIN GROUP BY with derived table and CTE forms.

-- SQL INNER JOIN with SELECT GROUP BY query - derived table

-- SQL sequence numbering groups

USE Northwind

SELECT   o.OrderID,

         SeqNo,

         ProductName,

         o.UnitPrice,

         o.Quantity,

         Amount = o.UnitPrice * o.Quantity,

         Discount = convert(DECIMAL(3,2),Discount)

FROM     Products p

         INNER JOIN [Order Details] o

           ON p.ProductID = o.ProductID

         INNER JOIN (SELECT   count(* ) AS SeqNo,

                        a.OrderID,

                        a.ProductID

               FROM     [Order Details] a

                        JOIN [Order Details] b

                          ON a.ProductID >= b.ProductID

                             AND a.OrderID = b.OrderID

               GROUP BY a.OrderID,

                        a.ProductID) seq

           ON o.OrderID = seq.OrderID

              AND o.ProductID = seq.ProductID

WHERE    o.Discount = 0.1

ORDER BY o.OrderID

GO

/* Partial results

OrderID     SeqNo ProductName                         UnitPrice

10248       1     Queso Cabrales                      14.00

10248       2     Singaporean Hokkien Fried Mee       9.80

10248       3     Mozzarella di Giovanni              34.80

10249       1     Tofu                                18.60

10249       2     Manjimup Dried Apples               42.40

10250       1     Jack's New England Clam Chowder     7.70

10250       2     Manjimup Dried Apples               42.40

*/

-- SQL INNER JOIN with SELECT GROUP BY query - CTE

;WITH CTE AS

(SELECT   count(* ) AS SeqNo,

                        a.OrderID,

                        a.ProductID

               FROM     [Order Details] a

                        JOIN [Order Details] b

                          ON a.ProductID >= b.ProductID

                             AND a.OrderID = b.OrderID

               GROUP BY a.OrderID,

                        a.ProductID) 

SELECT   o.OrderID,

         SeqNo,

         ProductName,

         o.UnitPrice,

         o.Quantity,

         Amount = o.UnitPrice * o.Quantity,

         Discount = convert(DECIMAL(3,2),Discount)

FROM     Products p

         INNER JOIN [Order Details] o

           ON p.ProductID = o.ProductID

         INNER JOIN CTE

           ON o.OrderID = CTE.OrderID

              AND o.ProductID = CTE.ProductID

WHERE    o.Discount = 0.1

ORDER BY o.OrderID

GO