SQL selection criteria on grouped aggregate

Question

I'm trying to find a simple MySQL statement for the following two problems:

I have 4 tables: Employees, Customers, Orders, Products (Each entry in Orders contains a date, a reference one product, a quantity and a reference to a customer, and a reference to an Employee).

Now I'm trying to get all customers where the volume of sale (quantity * product.price) is bigger in 1996 than in 1995.

And: I want to list all Employees whose volume of sale is below the average volume of sale.

Any help would really be appreciated. I've managed to get the information using a php script but I think this can be done with some clever SQL Statements.

Can anybody help me?

---

Employee Table: ID# Name

Products Table: ID# NAME# PRICE

Orders Table: ODERID# CUSTOMERID # DATE # EMPLOYEE# PRODUCTID# QUANTITY

Answer 1

For the first part (assuming quite a bit about the schema):

SELECT Customers.ID
FROM Customers 
    LEFT JOIN orders AS o1 ON o1.CustomerID=Customers.ID AND YEAR(o1.DATE) = 1995
    LEFT JOIN products AS p1 ON p1.id = o1.productID
    LEFT JOIN orders AS o2 ON o2.CustomerID=Customers.ID AND YEAR(o2.DATE) = 1996
    LEFT JOIN products AS p2 ON p2.id = o2.productID
HAVING SUM(o1.quantity* p1.price) < SUM(o2.quantity*p2.price)

Answer 2

I don't know the database type you're using, so I'll use sqlserver. The 'Year' function is available on most databases, so you should be able to rewrite the query for your db in question.

I think this is the query which returns all customerid's + ordertotal for the customers which have a higher total in 1996 than in 1995, but I haven't tested it. Crucial is the HAVING clause, where you can specify a WHERE kind of clause based on the grouped result.

SELECT  o.CustomerId, SUM(o.Quantity * p.Price) AS Total
FROM    Orders o INNER JOIN Products p
     ON o.ProductId = p.ProductId
WHERE   YEAR(o.Date) == 1996
GROUP BY o.CustomerId
HAVING SUM(o.Quantity * p.Price) > 
(
    SELECT  SUM(o.Quantity * p2.Price) AS Total
    FROM  Orders o2 INNER JOIN Products p2
      ON o2.ProductId = p.ProductId
    WHERE  YEAR(o.Date) == 1995
      AND o2.CustomerId = o.CustomerId
    GROUP BY o.CustomerId
)

Answer 3

something like that:

1. select * from customers c where (select sum (o.quantity * p.price) from orders o, product p where o.productID = p.productID and o.dateyear = 1996 and o.customerID = c.customerID) < (select sum (o.quantity * p.price) from orders o, product p where o.productID = p.productID and o.dateyear = 1995 and o.customerID = c.customerID)

2. select * from employees e where (select avg (o.quantity * p.price) from orders o, product p where o.productID = p.productID and o.empID = e.EmpID) < (select avg (o.quantity * p.price) from orders o, product p where o.productID = p.productID)