473,854 Members | 1,802 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.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
Jul 20 '05 #1
5 44348
On Fri, 5 Mar 2004 10:43:46 +0000 (UTC), "Mike Bannon"
<mi***@dataform ation.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.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

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,

Jul 20 '05 #2

"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:t9******** *************** *********@4ax.c om...
On Fri, 5 Mar 2004 10:43:46 +0000 (UTC), "Mike Bannon"
<mi***@dataform ation.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.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
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,


Thanks, Steve, works a treat :-)
Mike
Jul 20 '05 #3
>> 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,
...);
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_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.
Jul 20 '05 #4
Steve Jorgensen (no****@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.Orde rYear, OrderStats.Orde rQtr,
AVG(OrderStats. OrderLines)


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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5
On Sun, 7 Mar 2004 22:26:56 +0000 (UTC), Erland Sommarskog <so****@algonet .se>
wrote:
Steve Jorgensen (no****@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.Orde rYear, OrderStats.Orde rQtr,
AVG(OrderStats. OrderLines)


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.


Thanks for correcting my oversight.
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
22112
by: dan | last post by:
this is a program to count average letters per word. i am able to count the total number of letters, but not words. How do you count the total number of words in a text file, so i am able to divide the total letters divided by words. #include <iostream> #include <fstream> #include <cstdlib> #include <cctype>
5
5318
by: Stephen Miller | last post by:
Hi, I am trying to add a staggered running total and average to a query returning quarterly CPI data. I need to add 4 quarterly data points together to calculate a moving 12-month sum (YrCPI), and then to complicate things, calculate a moving average of the 12-month figure (AvgYrCPI). Given the sample data:
4
3414
by: Gary | last post by:
Hi, I have a temperature conversion program down pat, but I was told to add an average, meaning, i need to get the average temperature for as many times as it was entered. i do not know where to start, and my stpid book doesnt seem to help. here is my program #include <iostream.h> void main( ) {
3
4146
by: mochatrpl | last post by:
I am looking for a way to make a query / report display the running average for total dollars. I have already set up a query to provide totals dollars per day from which a report graphly shows the dollars per week. How do I then take the dollars and get a running average for the year? - Randy
4
6546
by: gaga | last post by:
hi guys, a part of my program requires me to calculate an average of items that are sold. the easiest way to do that would be writing a function, but im having trouble making up the parameters. if you can point me which elements to use when performing such a task, i would really appreciate it. thanks
13
4912
by: vaneric | last post by:
hi i have a set of RGB images of diff faces (of people )as a 2 dim numpyarray ...something like threefaces=array(, , ]) where xa1,xa2,xa3 are tuples each representing rgb values of a pixel of first image ..
6
5863
by: kumarboston | last post by:
Hi All, I am trying to get an average value for my data, here is my data file DATA FILE EP1934.PDB 250 250 11.27 EP1934.PDB 251 251 12.7332 EP1934.PDB 252 252 6.38341 EP1934.PDB 253 253 8.04318 EP1934.PDB 254 254 13.7123
2
5663
by: jac130 | last post by:
I have an array, populated with student grades. there is a list of student names, but their grades are stored in a class level array. there is a calculate average button,that is supposed to calculate the grades in the array. But my button only returns the last grade that was entered, not the average, here is my code: public Class btnAdd Dim grades() As Double Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As...
5
2152
by: kumarboston | last post by:
Hi all, I was trying to calculate the average value from different parts of the same data file. For example, if suppose we have number 1 - 10 and i was trying to calculate the average of only first 3 values and then 4 values and then last 3 value and then calculate the three averages. I have written a code but I guess it is very good way to calculate it and sometimes i get garbage values also. Here is a data file: 1 2 3 4 5
1
8291
by: Ormazd | last post by:
Hello, I was wondering if anyone might be able to help me with a little PERL script? I'm very new and I have been given a task to write a simple Perl script that prints out the file names and file sizes and determines the average file size from the directory listing found in the "files.txt" file. In other words, I need to add up the size of all of the files listed in the text document and divide by the number of files listed. Sample of...
0
9754
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11044
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10375
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9526
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7927
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7084
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5952
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4567
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4168
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.