SQL Primer: Using SUM(), GROUP BY and ORDER BY


This Recordset shows the itemized Orders2 for each OrderID.
OrderID ProductID ProductName Quantity UnitPrice TotalOrder
1 79 Polar Star 1 $ 310 $ 310
1 78 North Face Sunspot 2 $ 390 $ 780
1 84 Sierras 5 $ 87 $ 435
2 84 Sierras 1 $ 87 $ 87
2 80 Big Sur 1 $ 200 $ 200
2 79 Polar Star 1 $ 310 $ 310
2 82 Everglades 2 $ 98 $ 196
3 84 Sierras 3 $ 87 $ 261
3 82 Everglades 1 $ 98 $ 98
4 79 Polar Star 2 $ 310 $ 620
5 82 Everglades 1 $ 98 $ 98
6 79 Polar Star 1 $ 310 $ 310
7 82 Everglades 1 $ 98 $ 98
7 80 Big Sur 2 $ 200 $ 400
8 80 Big Sur 1 $ 200 $ 200
8 78 North Face Sunspot 2 $ 390 $ 780
8 83 Rockies 1 $ 78 $ 78
9 82 Everglades 1 $ 98 $ 98
10 79 Polar Star 1 $ 310 $ 310
11 82 Everglades 1 $ 98 $ 98
12 79 Polar Star 2 $ 310 $ 620
13 84 Sierras 1 $ 87 $ 87
14 82 Everglades 2 $ 98 $ 196

The SQL Source of the Recordset is as follows:

SELECT Order_Details2.OrderID, Order_Details2.ProductID, Products.ProductName, Order_Details2.Quantity, Order_Details2.UnitPrice, (Quantity*Order_Details2.UnitPrice) AS TotalOrder FROM Order_Details2 INNER JOIN Products ON Order_Details2.ProductID = Products.ProductID ORDER BY Order_Details2.OrderID

This example also uses INNER JOIN to relate the Order_Details2 and Products tables ON the ProductID field, and uses the ProductName values from the Products table. The sample also uses a calculated field (TotalOrder).

Go Top   |   Return to DB101 ADO Samples