Beginning C# 2005 Databases From Novice to Professional phần 7

Dữ liệu lưới điện xem và ba biểu tượng khay 12. Thực hiện dự án này khởi động, và chạy nó với Ctrl + F5. Bạn sẽ thấy một kết quả tương tự như một hiển thị trong hình 9-12. Hiển thị các khách hàng bị ràng buộc đến một cái nhìn lưới dữ liệu Nó hoạt động giống như ma thuật. Bạn đã đi qua rất nhiều màn hình, nhưng đã viết | CHAPTER 11 LEARNING MORE ABOUT QUERIES 287 Other Aggregates SQL has several built-in functions that aggregate the values of a column. Aggregate functions return a single value. For example you can use the aggregate functions to calculate the total number or average value of orders placed. You can find the order with the least value or the most expensive order. Aggregate functions as their name indicates work on a set of records and then calculate the appropriate aggregated value. SUM MIN MAX AVG and COUNT are frequently used aggregate functions. Try It Out Using the MIN MAX and AVG Functions Let s find the minimum maximum and average number of items of each product from the Order Details table 1. Enter the following query into SSMSE and execute it. You should see the results shown in Figure 11-7 select productid min quantity max quantity avg quantity from order details group by productid order by productid Product ID Minimum Maximum Average 288 CHAPTER 11 LEARNING MORE ABOUT QUERIES Figure 11-7. Using aggregate functions How It Works You use the MIN and MAX functions to find the minimum and maximum values and you use the AVG function to calculate the average value min quantity Minimum max quantity Maximum avg quantity Average Since you want the results listed by product you use the GROUP BY clause. From the result set you see that product 1 has a minimum order quantity of 2 a maximum order quantity of 80 and an average order quantity of 21. Note You use an ORDER BY clause to assure the results are in product ID sequence. As with DISTINCT some DBMSs would have inferred this sequence from the GROUP BY clause. But in general unless you explicitly use ORDER BY you can t predict the sequence of the rows in a result set. CHAPTER 11 LEARNING MORE ABOUT QUERIES 289 Datetime Functions Although the SQL standard defines a DATETIME data type and its components YEAR MONTH DAY HOUR MINUTE and SECOND it doesn t dictate how a DBMS makes this data available. Each DBMS offers a .