How To Do An Average Of A Count

Hi All

We have an order processing database which includes the standard Order
Header/Order Lines tables. I'm trying to write a query to get the average
number of lines per order over a given period, and I'm stuck :-(

I can get the number of lines per order with:

SELECT COUNT(*) FROM OrderDetails
INNER JOIN Order Header ON
OrderHeader.OrderNo = OrderDetails.OrderNo
WHERE OrderHeader.OrderDate .... {various criteria} ...
GROUP BY OrderDetails.OrderNumber

But how do I then get an average of this for the period?

TIA
Mike BannonOn Fri, 5 Mar 2004 10:43:46 +0000 (UTC), "Mike Bannon"
<mikeb@.dataformation.co.uk> wrote:

>Hi All
>We have an order processing database which includes the standard Order
>Header/Order Lines tables. I'm trying to write a query to get the average
>number of lines per order over a given period, and I'm stuck :-(
>I can get the number of lines per order with:
>SELECT COUNT(*) FROM OrderDetails
>INNER JOIN Order Header ON
>OrderHeader.OrderNo = OrderDetails.OrderNo
>WHERE OrderHeader.OrderDate .... {various criteria} ...
>GROUP BY OrderDetails.OrderNumber
>But how do I then get an average of this for the period?
>TIA
>Mike Bannon

Use a derived table. A derived table is a complete SELECT statement in
parentheses and followed by an alias name that is placed in the FROM clause of
another query. In your case, it might be something like this...

SELECT OrderStats.OrderYear, OrderStats.OrderQtr, AVG(OrderStats.OrderLines)
FROM (
SELECT
OrderDetails.OrderYear,
OrderDetails.OrderQtr,
OrderLines = COUNT(*)
FROM OrderDetails
INNER JOIN Order Header ON
OrderHeader.OrderNo = OrderDetails.OrderNo
WHERE OrderHeader.OrderDate .... {various criteria} ...
GROUP BY
OrderDetails.OrderYear,
OrderDetails.OrderQtr,
OrderDetails.OrderNumber
) OrderStats
GROUP BY
OrderStats.OrderYear,
OrderStats.OrderQtr,|||"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:t9ng40puunmh9bkf58iffbi6k53qggabno@.4ax.com...
> On Fri, 5 Mar 2004 10:43:46 +0000 (UTC), "Mike Bannon"
> <mikeb@.dataformation.co.uk> wrote:
> >Hi All
> >We have an order processing database which includes the standard Order
> >Header/Order Lines tables. I'm trying to write a query to get the average
> >number of lines per order over a given period, and I'm stuck :-(
> >I can get the number of lines per order with:
> >SELECT COUNT(*) FROM OrderDetails
> >INNER JOIN Order Header ON
> >OrderHeader.OrderNo = OrderDetails.OrderNo
> >WHERE OrderHeader.OrderDate .... {various criteria} ...
> >GROUP BY OrderDetails.OrderNumber
> >But how do I then get an average of this for the period?
> >TIA
> >Mike Bannon
>
> Use a derived table. A derived table is a complete SELECT statement in
> parentheses and followed by an alias name that is placed in the FROM
clause of
> another query. In your case, it might be something like this...
> SELECT OrderStats.OrderYear, OrderStats.OrderQtr,
AVG(OrderStats.OrderLines)
> FROM (
> SELECT
> OrderDetails.OrderYear,
> OrderDetails.OrderQtr,
> OrderLines = COUNT(*)
> FROM OrderDetails
> INNER JOIN Order Header ON
> OrderHeader.OrderNo = OrderDetails.OrderNo
> WHERE OrderHeader.OrderDate .... {various criteria} ...
> GROUP BY
> OrderDetails.OrderYear,
> OrderDetails.OrderQtr,
> OrderDetails.OrderNumber
> ) OrderStats
> GROUP BY
> OrderStats.OrderYear,
> OrderStats.OrderQtr,

Thanks, Steve, works a treat :-)
Mike|||>> We have an order processing database which includes the standard
Order
Header/Order Lines tables. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. My guess is that the order details looks like this:

CREATE TABLE OrderDetails
(order_nbr INTEGER NOT NULL
REFERENCES Orders(order_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
upc CHAR(10) NOT NULL -- or other industry code
REFERENCES Inventory(upc)
ON UPDATE CASCADE
ON DELETE CASCADE,
qty INTEGER NOT NULL,
...);

>> I'm trying to write a query to get the average number of lines per
order over a given period, and I'm stuck :-( <<

I hope that you mean the average number of **items** per order over a
given period. The way you said this would imply that you are copying
the input form (where lines are PHYSICAL), and have no proper LOGICAL
model. If I order my eggs one at a time, I get 12 lines; If I map the
order into a LOGICAL row, I get one row with a quantity of 12 eggs
(and then I can look for packaging units, discounts, etc.)

SELECT AVG(X.tally) AS avg_per_order
FROM (SELECT COUNT(*)
FROM OrderDetails AS D1
WHERE D1.order_nbr
IN (SELECT O1.order_nbr
FROM Orders AS O1
WHERE O1.order_dt
BETWEEN @.my_start_dateAND @.my_end_date)
GROUP BY order_nbr) AS X(tally);

The trick for aggregates inside aggregates is to use a derived table
for the lower level aggregations, then nest them outward.|||Steve Jorgensen (nospam@.nospam.nospam) writes:
> Use a derived table. A derived table is a complete SELECT statement in
> parentheses and followed by an alias name that is placed in the FROM
> clause of another query. In your case, it might be something like
> this...
> SELECT OrderStats.OrderYear, OrderStats.OrderQtr,
> AVG(OrderStats.OrderLines)

In case you want an average with a fraction, permit me to suggest an
improvement:

SELECT OrderStats.OrderYear, OrderStats.OrderQtr,
AVG(1.0 * OrderStats.OrderLines)

Without 1.0 you will get an integer result.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Sun, 7 Mar 2004 22:26:56 +0000 (UTC), Erland Sommarskog <sommar@.algonet.se>
wrote:

>Steve Jorgensen (nospam@.nospam.nospam) writes:
>> Use a derived table. A derived table is a complete SELECT statement in
>> parentheses and followed by an alias name that is placed in the FROM
>> clause of another query. In your case, it might be something like
>> this...
>>
>> SELECT OrderStats.OrderYear, OrderStats.OrderQtr,
>> AVG(OrderStats.OrderLines)
>In case you want an average with a fraction, permit me to suggest an
>improvement:
> SELECT OrderStats.OrderYear, OrderStats.OrderQtr,
> AVG(1.0 * OrderStats.OrderLines)
>Without 1.0 you will get an integer result.

Thanks for correcting my oversight.