TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
 Specialized Dev Zones Research Center eBook Library .NET Java C++ Web Dev Architecture Database Security Open Source Enterprise Mobile Special Reports 10-Minute Solutions DevXtra Blogs Slideshow

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.

 Home » Tip Bank » Database Development » SQL » Transact-sql
Language: SQL Server
Expertise: Beginner
Mar 25, 1997

### WEBINAR:On-Demand

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

# Calculating Averages When Joins Are Required to Filter Data

Question:
[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:

```    CREATE TABLE Titles
(product_id INTEGER NOT NULL PRIMARY KEY,
title INTEGER NOT NULL,
issue INTEGER NOT NULL,
issue_year INTEGER NOT NULL);

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

CREATE TABLE Customers
(custno INTEGER NOT NULL PRIMARY KEY
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).
or
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')
OR
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
71062.1056@compuserve.com

DevX Pro

 Submit a Tip Browse "Database Development" Tips Browse All Tips
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