How To Do An Average Of A Count

[Mike Bannon]

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.Ord erNo = OrderDetails.Or derNo
WHERE OrderHeader.Ord erDate ..... {various criteria} ...
GROUP BY OrderDetails.Or derNumber

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

TIA
Mike Bannon

[Steve Jorgensen]

Re: How To Do An Average Of A Count

On Fri, 5 Mar 2004 10:43:46 +0000 (UTC), "Mike Bannon"
<mikeb@dataform ation.co.uk> wrote:
[color=blue]
>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.Or derNo = OrderDetails.Or derNo
>WHERE OrderHeader.Ord erDate ..... {various criteria} ...
>GROUP BY OrderDetails.Or derNumber
>
>But how do I then get an average of this for the period?
>
>TIA
>Mike Bannon
>[/color]


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.Orde rYear, OrderStats.Orde rQtr, AVG(OrderStats. OrderLines)
FROM (
SELECT
OrderDetails.Or derYear,
OrderDetails.Or derQtr,
OrderLines = COUNT(*)
FROM OrderDetails
INNER JOIN Order Header ON
OrderHeader.Ord erNo = OrderDetails.Or derNo
WHERE OrderHeader.Ord erDate ..... {various criteria} ...
GROUP BY
OrderDetails.Or derYear,
OrderDetails.Or derQtr,
OrderDetails.Or derNumber
) OrderStats
GROUP BY
OrderStats.Orde rYear,
OrderStats.Orde rQtr,

[Mike Bannon]

Re: How To Do An Average Of A Count


"Steve Jorgensen" <nospam@nospam. nospam> wrote in message
news:t9ng40puun mh9bkf58iffbi6k 53qggabno@4ax.c om...[color=blue]
> On Fri, 5 Mar 2004 10:43:46 +0000 (UTC), "Mike Bannon"
> <mikeb@dataform ation.co.uk> wrote:
>[color=green]
> >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.Or derNo = OrderDetails.Or derNo
> >WHERE OrderHeader.Ord erDate ..... {various criteria} ...
> >GROUP BY OrderDetails.Or derNumber
> >
> >But how do I then get an average of this for the period?
> >
> >TIA
> >Mike Bannon
> >[/color]
>
>
> 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[/color]
clause of[color=blue]
> another query. In your case, it might be something like this...
>
> SELECT OrderStats.Orde rYear, OrderStats.Orde rQtr,[/color]
AVG(OrderStats. OrderLines)[color=blue]
> FROM (
> SELECT
> OrderDetails.Or derYear,
> OrderDetails.Or derQtr,
> OrderLines = COUNT(*)
> FROM OrderDetails
> INNER JOIN Order Header ON
> OrderHeader.Ord erNo = OrderDetails.Or derNo
> WHERE OrderHeader.Ord erDate ..... {various criteria} ...
> GROUP BY
> OrderDetails.Or derYear,
> OrderDetails.Or derQtr,
> OrderDetails.Or derNumber
> ) OrderStats
> GROUP BY
> OrderStats.Orde rYear,
> OrderStats.Orde rQtr,
>[/color]

Thanks, Steve, works a treat :-)
Mike

[--CELKO--]

Re: How To Do An Average Of A Count

>> 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_nb r)
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,
...);
[color=blue][color=green]
>> I'm trying to write a query to get the average number of lines per[/color][/color]
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_dateA ND @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.

[Erland Sommarskog]

Re: How To Do An Average Of A Count

Steve Jorgensen (nospam@nospam. nospam) writes:[color=blue]
> 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.Orde rYear, OrderStats.Orde rQtr,
> AVG(OrderStats. OrderLines)[/color]

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

SELECT OrderStats.Orde rYear, OrderStats.Orde rQtr,
AVG(1.0 * OrderStats.Orde rLines)

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/techinfo/productdoc/2000/books.asp

[Steve Jorgensen]

Re: How To Do An Average Of A Count

On Sun, 7 Mar 2004 22:26:56 +0000 (UTC), Erland Sommarskog <sommar@algonet .se>
wrote:
[color=blue]
>Steve Jorgensen (nospam@nospam. nospam) writes:[color=green]
>> 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.Orde rYear, OrderStats.Orde rQtr,
>> AVG(OrderStats. OrderLines)[/color]
>
>In case you want an average with a fraction, permit me to suggest an
>improvement:
>
> SELECT OrderStats.Orde rYear, OrderStats.Orde rQtr,
> AVG(1.0 * OrderStats.Orde rLines)
>
>Without 1.0 you will get an integer result.[/color]

Thanks for correcting my oversight.