Advanced Aggregation Functions

ProductName	QuantityPerUnit	UnitPrice	UnitsInStock	UnitsOnOrder	Row Total	Row Max	Row Min	Row Avg
vvbvcbcbcbii	10 boxes x 20 bags	18	39	0	1,077.00	1,020.00	0.00	269.25
Chang55dd	24 - 12 oz bottles	19	17	40	100.00	40.00	17.00	25.00
Syrddu	12 - 550 ml bottles	10	13	70	105.00	70.00	10.00	26.25
Chef Anton's Cajun Seasoning	48 - 6 oz jars	22	53	0	123.00	53.00	0.00	30.75
asdf	36 boxes	21.35	20	0	77.35	36.00	0.00	19.34
Grandma's Boysenberry Spread	12 - 8 oz jars	25	120	0	157.00	120.00	0.00	39.25
Uncle Bob's Organic Dried Pears	12 - 1 lb pkgs.	30	15	0	57.00	30.00	0.00	14.25
Northwoods Cranberry Sauce	12 - 12 oz jars	40	6	0	58.00	40.00	0.00	14.50
Mishi Kobe Niku	18 - 500 g pkgs.	97	29	0	144.00	97.00	0.00	36.00
Ikura	12 - 200 ml jars	31	31	0	74.00	31.00	0.00	18.50
Queso Cabrales	1 kg pkg.	21	22	30	74.00	30.00	1.00	18.50
Queso Manchego La Pastora	10 - 500 g pkgs.	38	86	0	134.00	86.00	0.00	33.50
Konbu	2 kg box	6	24	0	32.00	24.00	0.00	8.00
Tofu	40 - 100 g pkgs.	23.25	35	0	98.25	40.00	0.00	24.56
Genen Shouyu	24 - 250 ml bottles	15.5	39	0	78.50	39.00	0.00	19.63
Pavlova	32 - 500 g boxes	17.45	29	0	78.45	32.00	0.00	19.61
Alice Mutton	20 - 1 kg tins	39	10	0	69.00	39.00	0.00	17.25
Carnarvon Tigers	16 kg pkg.	62.5	42	0	120.50	62.50	0.00	30.13
Teatime Chocolate Biscuits	10 boxes x 12 pieces	9.2	25	0	1,046.20	1,012.00	0.00	261.55
Sir Rodney's Marmalade	30 gift boxes	81	40	0	151.00	81.00	0.00	37.75
Sir Rodney's Scones	24 pkgs. x 4 pieces	10	3	40	77.40	40.00	3.00	19.35
Gustaf's Knäckebröd	24 - 500 g pkgs.	21	104	0	149.00	104.00	0.00	37.25
Tunnbröd	12 - 250 g pkgs.	9	61	0	82.00	61.00	0.00	20.50
Guaraná Fantástica	12 - 355 ml cans	4.5	20	0	36.50	20.00	0.00	9.13
NuNuCa Nuß-Nougat-Creme	20 - 450 g glasses	14	76	0	110.00	76.00	0.00	27.50
Gumbär Gummibärchen	100 - 250 g bags	31.23	15	0	146.23	100.00	0.00	36.56
Schoggi Schokolade	100 - 100 g pieces	43.9	49	0	192.90	100.00	0.00	48.23
Rössle Sauerkraut	25 - 825 g cans	45.6	26	0	96.60	45.60	0.00	24.15
Thüringer Rostbratwurst	50 bags x 30 sausgs.	123.79	10	0	5,163.79	5,030.00	0.00	1,290.95
Nord-Ost Matjeshering	10 - 200 g glasses	25.89	10	0	45.89	25.89	0.00	11.47
Gorgonzola Telino	12 - 100 g pkgs	12.5	0	70	94.50	70.00	0.00	23.63
Mascarpone Fabioli	24 - 200 g pkgs.	32	9	40	105.00	40.00	9.00	26.25
Geitost	500 g	2.5	112	0	614.50	500.00	0.00	153.63
Sasquatch Ale	24 - 12 oz bottles	14	111	0	149.00	111.00	0.00	37.25
Steeleye Stout	24 - 12 oz bottles	18	20	0	62.00	24.00	0.00	15.50
Inlagd Sill	24 - 250 g jars	19	112	0	155.00	112.00	0.00	38.75
Gravad lax	12 - 500 g pkgs.	26	11	50	99.00	50.00	11.00	24.75
Côte de Blaye	12 - 75 cl bottles	263.5	17	0	292.50	263.50	0.00	73.13
Chartreuse verte	750 cc per bottle	18	69	0	837.00	750.00	0.00	209.25
Boston Crab Meat	24 - 4 oz tins	18.4	123	0	165.40	123.00	0.00	41.35
Jack's New England Clam Chowder	12 - 12 oz cans	9.65	85	0	106.65	85.00	0.00	26.66
Singaporean Hokkien Fried Mee	32 - 1 kg pkgs.	14	26	0	72.00	32.00	0.00	18.00
Ipoh Coffee	16 - 500 g tins	46	17	10	89.00	46.00	10.00	22.25
Gula Malacca	20 - 2 kg bags	19.45	27	0	66.45	27.00	0.00	16.61
Røgede sild	1k pkg.	9.5	5	70	85.50	70.00	1.00	21.38
Spegesild	4 - 450 g glasses	12	95	0	111.00	95.00	0.00	27.75
Zaanse koeken	10 - 4 oz boxes	9.5	36	0	55.50	36.00	0.00	13.88
Chocolade	10 pkgs.	12.75	15	70	107.75	70.00	10.00	26.94
Maxilaku	24 - 50 g pkgs.	20	10	60	114.00	60.00	10.00	28.50
Valkoinen suklaa	12 - 100 g bars	16.25	65	0	93.25	65.00	0.00	23.31
Manjimup Dried Apples	50 - 300 g pkgs.	53	20	0	123.00	53.00	0.00	30.75
Filo Mix	16 - 2 kg boxes	7	38	0	61.00	38.00	0.00	15.25
Perth Pasties	48 pieces	32.8	10	0	90.80	48.00	0.00	22.70
Tourtière	16 pies	7.45	21	0	44.45	21.00	0.00	11.11
Pâté chinois	24 boxes x 2 pies	24	115	0	381.00	242.00	0.00	95.25
Gnocchi di nonna Alice	24 - 250 g pkgs.	38	21	10	93.00	38.00	10.00	23.25
Ravioli Angelo	24 - 250 g pkgs.	19.5	36	0	79.50	36.00	0.00	19.88
Escargots de Bourgogne	24 pieces	13.25	62	0	99.25	62.00	0.00	24.81
Raclette Courdavault	5 kg pkg.	55	79	0	139.00	79.00	0.00	34.75
Camembert Pierrot	15 - 300 g rounds	34	19	0	68.00	34.00	0.00	17.00
Sirop d'érable	24 - 500 ml bottles	28.5	113	0	165.50	113.00	0.00	41.38
Tarte au sucre	48 pies	49.3	17	0	114.30	49.30	0.00	28.58
Vegie-spread	15 - 625 g jars	43.9	24	0	82.90	43.90	0.00	20.73
Wimmers gute Semmelknödel	20 bags x 4 pieces	33.25	22	80	339.25	204.00	22.00	84.81
Louisiana Fiery Hot Pepper Sauce	32 - 8 oz bottles	21.05	76	0	129.05	76.00	0.00	32.26
Louisiana Hot Spiced Okra	24 - 8 oz jars	17	4	100	145.00	100.00	4.00	36.25
Laughing Lumberjack Lager	24 - 12 oz bottles	14	52	0	90.00	52.00	0.00	22.50
Scottish Longbreads	10 boxes x 8 pieces	12.5	6	10	136.50	108.00	6.00	34.13
Gudbrandsdalsost	10 kg pkg.	36	26	0	72.00	36.00	0.00	18.00
Outback Lager	24 - 355 ml bottles	15	15	10	64.00	24.00	10.00	16.00
Fløtemysost	10 - 500 g pkgs.	21.5	26	0	57.50	26.00	0.00	14.38
Mozzarella di Giovanni	24 - 200 g pkgs.	34.8	14	0	72.80	34.80	0.00	18.20
Röd Kaviar	24 - 150 g jars	15	101	0	140.00	101.00	0.00	35.00
Longlife Tofu	5 kg pkg.	10	4	20	39.00	20.00	4.00	9.75
Rhönbräu Klosterbier	24 - 0.5 l bottles	7.75	125	0	156.75	125.00	0.00	39.19
Lakkalikööri	500 ml	18	57	0	575.00	500.00	0.00	143.75
Original Frankfurter grüne Soße	12 boxes	13	32	0	57.00	32.00	0.00	14.25
sdg		0	0	0	0.00	0.00	0.00	0.00
ghgfhfgh		0	0	0	0.00	0.00	0.00	0.00
Aniseed Syru		0	0	0	0.00	0.00	0.00	0.00
gfdg		0	0	0	0.00	0.00	0.00	0.00
Col Total	10873 Items	2222.71	3,169.00 Units	780.00 Units	10,873.40
Col Max	5030	263.5	125	100
Col Min	1	0	0	0
Col Avg	141.21	27.44	39.12	9.63
Col Count	77	81	81	81

Advanced XY Aggregates

<script language="VB" runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
  Dim AggProp = "(~^:)fields=1^2^3^4|aggtype=SUM|bottomtemp=<tr align=right><td class=gh>Col Total</td><td class=CS>[[1:###]] Items</td><td class=CS>[[2:###.00]]</td><td class=CS>[[3:N]] Units</td><td class=CS>[[4:N]] Units</td><td class=CS align=right><b>[[GrandTotal:###,###.00]]</b></td><td class=CS colspan=3>&nbsp;</td></tr>|sumcolheader=<td class=gh><b>Row Total</b></td>|headertag=class=CS align=right|cellformat=Val"
  AggProp &= "~fields=1^2^3^4|aggtype=MAX|bottomtemp=<tr align=right><td class=gh>Col Max</td><td class=CS>[[1]]</td><td class=CS>[[2]]</td><td class=CS>[[3]]</td><td class=CS>[[4]]</td><td class=CS colspan=4>&nbsp;</td></tr>|sumcolheader=<td class=gh>Row Max</td>|headertag=class=CS align=right|cellformat=Val"
  AggProp &= "~fields=1^2^3^4|aggtype=MIN|bottomtemp=<tr align=right><td class=gh>Col Min</td><td class=CS>[[1]]</td><td class=CS>[[2]]</td><td class=CS>[[3]]</td><td class=CS>[[4]]</td><td class=CS colspan=4>&nbsp;</td></tr>|sumcolheader=<td class=gh><b>Row Min</b></td>|headertag=class=CS align=right|cellformat=Val"
  AggProp &= "~fields=1^2^3^4|aggtype=AVG|bottomtemp=<tr align=right><td class=gh>Col Avg</td><td class=CS>[[1:f]]</td><td class=CS>[[2:f]]</td><td class=CS>[[3:f]]</td><td class=CS>[[4:f]]</td><td class=CS colspan=4>&nbsp;</td></tr>|sumcolheader=<td class=gh>Row Avg</td>|headertag=class=CS align=right|cellformat=Val"
  AggProp &= "~fields=1^2^3^4|aggtype=COUNT|bottomtemp=<tr align=right><td class=gh>Col Count</td><td class=CS>[[1]]</td><td class=CS>[[2]]</td><td class=CS>[[3]]</td><td class=CS>[[4]]</td><td class=CS colspan=4>&nbsp;</td></tr>|cellformat=Val"
  Dim Agg As New Tornado.z
  With Agg
    .dbQP = "U=33| S=33| PS=-1| D=Nwind.mdb| ni=none| Th=Tit=Advanced Aggregation Functions"
    .dbGridMagicCell = "fi=1|tag=align=right|fi=2|tag=align=right|fi=3|tag=align=right|fi=4|tag=align=right"
    .dbSQL = "Select ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder FROM Products"
    .dbAggregate = AggProp
    .ASPdbNET()
  End With
End Sub
</script>

Example from Special Features - Advanced XY Aggregates