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

 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 Within Arbitrary Groups

Question:
[Joe Celko's Production Report Puzzle]

We get a production report from work centers which has a date, center code and how many widgets were produced from each batch of raw materials sent to them that day. It looks like this:

```    CREATE TABLE Production
(center INTEGER NOT NULL,
wkdate DATE NOT NULL,
batchno INTEGER NOT NULL,
widgets INTEGER NOT NULL,
PRIMARY KEY (center, wkdate, batchno));
```
The boss comes in and says he wants to know the average number of widgets Produced by date and center. We say no problem and do it. The next day he comes back and wants the same data in groups of three. In other words, if on 1994 Feb 24 in center 42, we processed nine batches, the report will show the average number of widgets made from the first three batches, the second three batches and the last three batches.

The first query is very straightforward:

```    SELECT center, wkdate, COUNT(batchno), AVG(widgets)
FROM Production
GROUP BY center, wkdate;
```
We have to make some assumptions about the second query and decide how to handle daily work where the number of batches is not evenly divisible by three. I am throwing anyone on the border into the lower third; thus, eight batches would be in ordered thirds of 3, 3 and 2 batches each. We can assume the batches are numbered from 1 to (n) for each day's work and make life easier.

Using the CASE expression in SQL-92, we can find which third a batchno is in with a VIEW, thus:

```    CREATE VIEW Prod3 (center, wkdate, third, widgets)
AS SELECT center, wkdate,
CASE WHEN batchno <= MAX(batchno)/3 THEN 1
WHEN batchno > (2*MAX(batchno))/3 THEN 3
ELSE 2
END, widgets
FROM Production;
```
If you do not have this in your SQL, then you might try something like this:
```    CREATE VIEW Prod3 (center, wkdate, third, batchno, widgets)
AS SELECT center, wkdate, 1, batchno, widgets
FROM Production AS P1
WHERE batchno <= (SELECT MAX(batchno)
FROM Production AS P2
WHERE P1.center = P2.center
AND P1.wkdate = P2.wkdate)
UNION
SELECT center, wkdate, 2, batchno, widgets
FROM Production AS P1
WHERE batchno > (SELECT MAX(batchno)
FROM Production AS P2
WHERE P1.center = P2.center
AND P1.wkdate = P2.wkdate)
AND batchno <= (SELECT 2 * MAX(batchno)
FROM Production AS P2
WHERE P1.center = P2.center
AND P1.wkdate = P2.wkdate)
UNION
SELECT center, wkdate, 3, batchno, widgets
FROM Production AS P1
WHERE batchno > (SELECT 2 * MAX(batchno)
FROM Production AS P2
WHERE P1.center = P2.center
AND P1.wkdate = P2.wkdate);
```
Either way, we end up with the final query:
```    SELECT center, wkdate, third, COUNT(batchno), AVG(widgets)
FROM Prod3
GROUP BY center, wkdate, third;
```

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