Time summaries from multi-entity database

Question

I have the following Database Schema (re image),

And I'm trying to write a query which would return results something like this... (Column Headings): Product_Group - Product_Name - Week1 - Week2 - Week3 - Week4 - QTY_Order

Week1 are all the sales of a particular product between the current Date and the previous 7 days. Week2 are all the sales of a particular product from 2 weeks ago (thus between currentdate -7days AND currentdate -14 days)
Week3 are all the sales of a particular product from 3 weeks ago... etc;
QTY_Order is to be the average of a particular products sales of Week1, Week2, Week3 & Week4.

The following SQL will produce: Product_Group - Product_Name - Week1 - QTY_Order (but quantity_order is incorrect).

SELECT Product.ProductGroupId, Product.Name, SUM(SaleLine.Quantity) AS Week1, SUM(SaleLine.Quantity) AS OrderQty 
FROM Sale 
JOIN SaleLine ON Sale.ID = SaleLine.SaleId 
JOIN Product ON SaleLine.ProductId = Product.Id 
WHERE Sale.SaleDateTime > (CURDATE() - INTERVAL 7 DAY ) 
GROUP BY Product.ProductGroupId, Product.Name;

I've also tried the following SQL to get: Product_Name - Week1 - Week2 - QTY_Order (again, qty_order is incorrect), but week2 returns all null's.

SELECT Product.ProductGroupId, Product.Name, SUM(SaleLine.Quantity) AS Week1, (SELECT SUM(SaleLine.Quantity) 
    FROM SaleLine 
    JOIN Sale ON SaleLine.SaleID = Sale.ID 
    JOIN Product on SaleLine.ProductId = Product.Id 
    WHERE Sale.SaleDateTime BETWEEN (CURDATE() - INTERVAL 14 DAY) AND (CURDATE() - INTERVAL 7 DAY)) AS Week2, SUM(SaleLine.Quantity) AS QTY_TO_ORDER 
FROM Sale 
JOIN SaleLine ON Sale.ID = SaleLine.SaleId 
JOIN Product ON SaleLine.ProductId = Product.Id 
WHERE Sale.SaleDateTime > (CURDATE() - INTERVAL 7 DAY ) 
GROUP BY Product.ProductGroupId, Product.Name;

Can anyone point out where im going wrong with Week2 (that its returning Null).
Can anyone think of any easier/cleaner way to code this?
Can anyone provide some pointers for calculating QTY_Order?

Thanks in advance for any help people could provide :-)

**Edit - Added by Request (customer table not included):
Create Table Statements (there are also various triggers acting as constraints not included)

CREATE TABLE ProductGroup( Id int PRIMARY KEY AUTO_INCREMENT, Name varchar(50) NOT NULL );

CREATE TABLE Product( Id int PRIMARY KEY AUTO_INCREMENT, ProductGroupId int, Name varchar(100) NOT NULL, Price decimal(8,2), QuantityOnHand int NOT NULL, QuantitySold int NOT NULL, QuantityToOrder int NOT NULL, QuantityRequested int NOT NULL, CONSTRAINT FK_Product_ProductGroup FOREIGN KEY (ProductGroupId) REFERENCES ProductGroup(Id) );

CREATE TABLE Sale( Id int PRIMARY KEY AUTO_INCREMENT, CustomerId int, SaleDateTime datetime NOT NULL, CONSTRAINT Sale_Customer FOREIGN KEY (CustomerId) REFERENCES Customer(Id) );

CREATE TABLE SaleLine( Id int PRIMARY KEY AUTO_INCREMENT, ProductId int NOT NULL, SaleId int NOT NULL, Quantity int NOT NULL, CONSTRAINT FK_SaleLine_Product FOREIGN KEY (ProductId) REFERENCES Product(Id), CONSTRAINT FK_SaleLine_Sale FOREIGN KEY (SaleId) REFERENCES Sale(Id) );

Answer 1

You are seeking five summaries from your information: Week1, Week2, Week3, Week4, and QTY_Order. That makes for a complex GROUP BY query.

It's best to approach this sort of thing step by step. Your first two steps should be to write separate queries which generate correct Week1 and QTY_Order queries. That will help you get your business logic right. Do you want to count orders? Do you want to sum units sold? Do you want to sum gross revenue?

(I'd rather not attempt to write such queries for you; it's your data, not mine.)

Once you have that sort of thing right, you can do something like this pseudocode to get your weekly summaries.

  SELECT group, name,
         SUM( CASE WHEN saledate < CURDATE() - INTERVAL 0 DAY
                    AND saledate >= CURDATE() - INTERVAL 7 DAY THEN Quantity
                   ELSE 0 END) Week1,
         SUM( CASE WHEN saledate < CURDATE() - INTERVAL 7 DAY
                    AND saledate >= CURDATE() - INTERVAL 14 DAY THEN Quantity
                   ELSE 0 END) Week2,
         SUM( CASE WHEN saledate <  CURDATE() - INTERVAL 14 DAY
                    AND saledate >= CURDATE() - INTERVAL 21 DAY THEN Quantity
                   ELSE 0 END) Week3
    FROM (some subquery)
   GROUP BY group,name

The trick is using case statements inside your sum functions to select transactions for the appropriate week.

It's a hairball of a query. But it will work.