Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL Server
Expertise: Beginner
Mar 25, 1997



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Calculating Averages When Joins Are Required to Filter Data

[Joe Celko's Magazine Sales Puzzle]

This one was posted on the Sybase forum of CompuServe by Keith McGregor in November of 1994.

One of his end users came to him with the following query. After nearly three days of trial and error, he still didn't have a clue how to tell her to do it. He could have done this in about 30 minutes using COBOL and flat files.

You are given the following tables for a magazine distribution database:

        (product_id INTEGER NOT NULL PRIMARY KEY,
        title INTEGER NOT NULL,
        issue INTEGER NOT NULL,
        issue_year INTEGER NOT NULL);

        (product_id INTEGER NOT NULL,
        custno INTEGER NOT NULL, 
        net_sold_qty INTEGER NOT NULL, 
        PRIMARY KEY (product_id, customer));

    CREATE TABLE Customers
        customer_name CHAR (20) NOT NULL);
He needs to select the customer_name(s) where:
a) The average net_sold_qty is greater than two for BOTH title 02667 and 48632 (if the average is two or less for either one, do not select the customer at all).
b) The average net_sold_qty is greater than five for title 01107 (if this is true, select the customer regardless of the result of condition a).

Let's create a view of the three tables joined together which will give us the basic information we are after. Maybe this view can be used for other reports later.

    CREATE VIEW Magazine(customer_name, title, net_sold_qty)
        AS SELECT Sales.customer_name, Titles.title, net_sold_qty
            FROM Titles, Sales, Customers
            WHERE Sales.custno = Customers.custno 
                AND Titles.product_id = Sales.product_id;
Then we write the query from hell:
    SELECT customer_name
        FROM Magazines AS M0
        GROUP BY customer_name 
        HAVING  -- the two accept conditions
            (5 < (SELECT AVG(net_sold_qty)
                FROM Magazines AS M1
                WHERE M1.custno = M0.custno 
                    AND title = '01107'))
            OR (2 < (SELECT AVG(net_sold_qty)
                FROM Magazines AS M2
                WHERE M2.custno = M0.custno 
                    AND title IN ('02667', '48632'))) 
        AND NOT -- the two reject conditions
            ( 2 > (SELECT AVG(net_sold_qty)
                FROM Magazines AS M3
                WHERE M3.custno = M0.custno 
                    AND title IN = '02667')
            2 > (SELECT AVG(net_sold_qty)
                FROM Magazines AS M4
                WHERE M4.custno = M0.custno 
                    AND title = '48632')
For bonus points, can any reader simplify or improve this expression? Hint: DeMorgan's law might be useful and it would help to have a decision table.

The solution provided to the puzzle can be greatly simplified by using two techniques: First create a VIEW of the average sales and include an 'EXISTS' for the condition of two titles which both must exceed a threshold.

The DML:

    CREATE VIEW Magazines (custno, title, avg_qty_sold) AS
        SELECT Sales.custno, Titles.title, AVG(Sales.net_sold_qty)
            FROM Titles, Customers, Sales
            WHERE Titles.product_id = Sales.product_id
                AND Customers.custno = Sales.custno
                AND Titles.title IN (01107, 02667, 48632)
            GROUP BY Sales.custno, Titles.title;

    SELECT DISTINCT Customers.customer_name
        FROM Magazines, Customers
        WHERE Customers.custno = Magazines.custno
            AND ((Magazines.title = 1107 AND Magazines.avg_qty_sold > 5)
            OR (Magazines.title = 2667 AND Magazines.avg_qty_sold > 2
            AND EXISTS
                (SELECT 1
                    FROM Magazines, Other
                    WHERE Other.title = 48632
                        AND Other.custno = Magazines.custno
                        AND Other.avg_qty_sold > 2)));

Puzzle provided courtesy of:
Joe Celko

DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date