产品销售报告每周一次[关闭]

Question

how can i run a SQL query to retrieve weekly and monthly sales reports.

Table: orders

ID |Product_id | order price | QTY | purchase date

1        1           34         2        9/10/12
2        1           34         2        8/10/12
3        4           34         2        07/10/12
4        3           34         2        22/09/12
5        1           34         2        9/10/12
6        1           34         2        22/09/12
7        4           34         2        9/08/12
8        3           34         2        22/09/12

Table: Products

ID | Name | description

1    A       ksfjsdkf
2    B       ksfjsdkf
3    C       ksfjsdkf
1    A       ksfjsdkf

Expecting result :the following is an example result not accurate

product_name           Weekly Sum(QTY), weekly revenue,  Monthly SUM(QTY), monthly revenue

A                           120             3000           2345                  234343

Answer 1

Ok here a simple Query for the Quantities and Order Price

SELECT p.Description, psw.qtysum, psw.opsum, psm.qtysum, psm.opsum
FROM Products p
LEFT JOIN
  ( SELECT sw.product_id, SUM( sw.QTY ) AS qtysum, SUM( sw.`order price` ) AS opsum 
    FROM orders sw 
    WHERE WEEK( sw.`purchase date`) = WEEK( current_date )
    AND YEAR( sw.`purchase date`) = YEAR( current_date )
    GROUP BY sw.product_id ) psw 
  ON p.id = psw.product_id

LEFT JOIN
  ( SELECT sm.product_id, SUM( sm.QTY ) AS qtysum, SUM( sm.`order price` ) AS opsum 
    FROM orders sm 
    WHERE MONTH( sm.`purchase date`) = MONTH( current_date )
    AND YEAR( sm.`purchase date`) = YEAR( current_date )
    GROUP BY sm.product_id ) psm 
  ON p.id = psm.product_id