Average Order size

[SkiNLaB]

I have an Order table,

OrderID
OrderDate
blah blah

and an OrderItem table
ItemID
OrderID <-- foreign key
Product
Cost
Quantity


And i cant work out how to put the average order size on the report!, can anyone help, this must be a common thing...

any questions, please ask

Edit:

eg. ""SELECT AVG(orderSize) as avgOrderSize FROM (SELECT sum(Cost) as orderSize FROM OrderItem GROUP BY OrderID)"

gets me what i want

[brucevde]

There are two ways.

From the Insert menu, select Summary field (if the menu item is disabled click the mouse in the details section and try again). A dialog box will be displayed allowing you to setup the summary. you need for your report. Watch out for that top box it tends to change automatically when you make a change elsewhere on this screen. This dialog will create summary field for each group and/or a grand total for the report.

You can also create a formula. For example this formula
Average ({[Order Details].Amount},{Orders.OrderID} )
returns the average of the amount column in the order detail table for each order.

[SkiNLaB]

Ill give that second option a go

but regarding the first option, if i insert a summary on the field OrderItem.Itemcost, and set it to average, that will give me an average of the item cost, not an ORDER cost.

[brucevde]

That depends on what you specify in the group box. If you specify the average summary is grouped by Order Id, then the Average will print in the Group Footer section of the Order. Also, if you check the box Insert Summary into all groups, a new field is added to every Group Footer automatically.

(sorry, I don't have Crystal handy to check on the actual names of the dialog prompts).

Just play around with the options in the dialog box, believe me you can get exactly what you want.

[SkiNLaB]

Ok well i tried putting the summary in a group, grouped on Order.OrderID, and i still dont get it!

i cant find anything similar to 'Insert Summary into all groups'

but anyway, what this does is put the avergage item cost FOR EACH order. what i need is the sum of that (easily done, just change the summary), now i need an average of all those numbers!

EDIT: and i tried that formula, but for some weird reason it wont display, it has nothing in it, but if i click browse data, it has a couple of values, !??!?!?

edit again: if i put it in the group, it just spits out the same number as the summary does in the footer

[brucevde]

I just realized, that I assumed that we were talking about Crystal Reports. If so what version, if not ??

[SkiNLaB]

yeah CR9

for the moment what i am doing is working out my values before i call the report, and then placing them in textboxes on the report, which isnt so bad i guess.......