Tham khảo tài liệu ' how to do everything with php (phần 6)', công nghệ thông tin, kỹ thuật lập trình phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | 234 How to Do Everything with PHP MySQL To find the most popular product the product with the maximum sales over the last year use the following query which groups and sums the orders by product type and then returns the product with the maximum number of orders mysql SELECT SUM AS totalSales - FROM products AS p orders AS o - WHERE - AND DATE_SUB NOW INTERVAL 1 YEAR - GROUP BY - ORDER BY totalSales DESC - LIMIT 0 1 --------------------------- ----------- productDesc totalSales --------------------------- ----------- Electronic tape measure 55 --------------------------- ----------- 1 row in set sec To see which products were never purchased in 2004 use a left join between the products and orders tables mysql SELECT - FROM products - LEFT JOIN orders ON - AND YEAR 2004 - WHERE IS NULL ------------- productDesc ------------- Alarm clock Toaster ------------- 2 rows in set sec And now for the big kahuna Figure 12-1 shows the total purchases made by each facility of each product within the current month together with the pre-tax and post-tax price and a subtotal. TEAM Line mysql SELECT as preTaxPrice SUM as tax-Percent 1 SUM 100 as postTaxPrice 1 SUM 100 o. orderQuantity as subTotal FROM facilities as f products as p orders as o products_taxes as pt taxes as t WHERE facilityId AND productId AND productId AND taxld GROUP BY facilityName 1 productDesc orderQuantity 1 preTaxPrice 1 taxPercent 1 postTaxPrice 1 subTotal 1 Head Office 1 Electric kettle 4 1 Head Office 1 Lawn mower 7 1 Texas .
