Advanced Aggregation Functions

ProductNameQuantityPerUnitUnitPriceUnitsInStockUnitsOnOrderRow TotalRow MaxRow MinRow Avg
aaaa 0.0000 0 0 0.000.000.000.00
2225 0.0000 0 0 0.000.000.000.00
MIcKE 0.0000 0 0 0.000.000.000.00
myold 0.0000 0 0 0.000.000.000.00
mydb 0.0000 0 0 0.000.000.000.00
trams 0.0000 0 0 0.000.000.000.00
trems 0.0000 0 0 0.000.000.000.00
greams 0.0000 0 0 0.000.000.000.00
geitost 0.0000 0 0 0.000.000.000.00
mese 0.0000 0 0 0.000.000.000.00
ggggg 0.0000 0 0 0.000.000.000.00
ffghfgh 0.0000 0 0 0.000.000.000.00
pen 0.0000 0 0 0.000.000.000.00
ddddd 0.0000 0 0 0.000.000.000.00
Chief 0.0000 0 0 0.000.000.000.00
Gajanan 0.0000 0 0 0.000.000.000.00
Na 0.0000 0 0 0.000.000.000.00
df 0.0000 0 0 0.000.000.000.00
dfdf 0.0000 0 0 0.000.000.000.00
sdf 0.0000 0 0 0.000.000.000.00
vvbvcbcbcbii 10 boxes x 20 bags 18 39 0 1,077.001,020.000.00269.25
Chang5 24 - 12 oz bottles 19 17 40 100.0040.0017.0025.00
BigOne 12 - 550 ml bottles 10 13 70 105.0070.0010.0026.25
Chef Anton's Cajun Seasoning 48 - 6 oz jars 22 53 0 123.0053.000.0030.75
asdf 36 boxes 21.35 20 0 77.3536.000.0019.34
Grandma's Boysenberry Spread 12 - 8 oz jars 25 120 0 157.00120.000.0039.25
Uncle Bob's Organic Dried Pears 12 - 1 lb pkgs. 30 15 0 57.0030.000.0014.25
Northwoods Cranberry Sauce 12 - 12 oz jars 40 6 0 58.0040.000.0014.50
Mishi Kobe Niku 18 - 500 g pkgs. 97 29 0 144.0097.000.0036.00
Ikura 12 - 200 ml jars 31 31 0 74.0031.000.0018.50
Queso Cabrales 1 kg pkg. 21 22 30 74.0030.001.0018.50
Queso Manchego La Pastora 10 - 500 g pkgs. 38 86 0 134.0086.000.0033.50
Konbu 2 kg box 6 24 0 32.0024.000.008.00
Tofu 40 - 100 g pkgs. 23.25 35 0 98.2540.000.0024.56
Genen Shouyu 24 - 250 ml bottles 15.5 39 0 78.5039.000.0019.63
Pavlova 32 - 500 g boxes 17.45 29 0 78.4532.000.0019.61
Alice Mutton 20 - 1 kg tins 39 10 0 69.0039.000.0017.25
Carnarvon Tigers 16 kg pkg. 62.5 42 0 120.5062.500.0030.13
Teatime Chocolate Biscuits 10 boxes x 12 pieces 9.2 25 0 1,046.201,012.000.00261.55
Sir Rodney's Marmalade 30 gift boxes 81 40 0 151.0081.000.0037.75
Sir Rodney's Scones 24 pkgs. x 4 pieces 10 3 40 77.4040.003.0019.35
Gustaf's Knäckebröd 24 - 500 g pkgs. 21 104 0 149.00104.000.0037.25
Tunnbröd 12 - 250 g pkgs. 9 61 0 82.0061.000.0020.50
Guaraná Fantástica 12 - 355 ml cans 4.5 20 0 36.5020.000.009.13
NuNuCa Nuß-Nougat-Creme 20 - 450 g glasses 14 76 0 110.0076.000.0027.50
Gumbär Gummibärchen 100 - 250 g bags 31.23 15 0 146.23100.000.0036.56
Schoggi Schokolade 100 - 100 g pieces 43.9 49 0 192.90100.000.0048.23
Rössle Sauerkraut 25 - 825 g cans 45.6 26 0 96.6045.600.0024.15
Thüringer Rostbratwurst 50 bags x 30 sausgs. 123.79 10 0 5,163.795,030.000.001,290.95
Nord-Ost Matjeshering 10 - 200 g glasses 25.89 10 0 45.8925.890.0011.47
Gorgonzola Telino 12 - 100 g pkgs 12.5 0 70 94.5070.000.0023.63
Mascarpone Fabioli 24 - 200 g pkgs. 32 9 40 105.0040.009.0026.25
Geitost 500 g 2.5 112 0 614.50500.000.00153.63
Sasquatch Ale 24 - 12 oz bottles 14 111 0 149.00111.000.0037.25
Steeleye Stout 24 - 12 oz bottles 18 20 0 62.0024.000.0015.50
Inlagd Sill 24 - 250 g jars 19 112 0 155.00112.000.0038.75
Gravad lax 12 - 500 g pkgs. 26 11 50 99.0050.0011.0024.75
Côte de Blaye 12 - 75 cl bottles 263.5 17 0 292.50263.500.0073.13
Chartreuse verte 750 cc per bottle 18 69 0 837.00750.000.00209.25
Boston Crab Meat 24 - 4 oz tins 18.4 123 0 165.40123.000.0041.35
Jack's New England Clam Chowder 12 - 12 oz cans 9.65 85 0 106.6585.000.0026.66
Singaporean Hokkien Fried Mee 32 - 1 kg pkgs. 14 26 0 72.0032.000.0018.00
Ipoh Coffee 16 - 500 g tins 46 17 10 89.0046.0010.0022.25
Gula Malacca 20 - 2 kg bags 19.45 27 0 66.4527.000.0016.61
Røgede sild 1k pkg. 9.5 5 70 85.5070.001.0021.38
Spegesild 4 - 450 g glasses 12 95 0 111.0095.000.0027.75
Zaanse koeken 10 - 4 oz boxes 9.5 36 0 55.5036.000.0013.88
Chocolade 10 pkgs. 12.75 15 70 107.7570.0010.0026.94
Maxilaku 24 - 50 g pkgs. 20 10 60 114.0060.0010.0028.50
Valkoinen suklaa 12 - 100 g bars 16.25 65 0 93.2565.000.0023.31
Manjimup Dried Apples 50 - 300 g pkgs. 53 20 0 123.0053.000.0030.75
Filo Mix 16 - 2 kg boxes 7 38 0 61.0038.000.0015.25
Perth Pasties 48 pieces 32.8 10 0 90.8048.000.0022.70
Tourtière 16 pies 7.45 21 0 44.4521.000.0011.11
Pâté chinois 24 boxes x 2 pies 24 115 0 381.00242.000.0095.25
Gnocchi di nonna Alice 24 - 250 g pkgs. 38 21 10 93.0038.0010.0023.25
Ravioli Angelo 24 - 250 g pkgs. 19.5 36 0 79.5036.000.0019.88
Escargots de Bourgogne 24 pieces 13.25 62 0 99.2562.000.0024.81
Raclette Courdavault 5 kg pkg. 55 79 0 139.0079.000.0034.75
Camembert Pierrot 15 - 300 g rounds 34 19 0 68.0034.000.0017.00
Sirop d'érable 24 - 500 ml bottles 28.5 113 0 165.50113.000.0041.38
Tarte au sucre 48 pies 49.3 17 0 114.3049.300.0028.58
Vegie-spread 15 - 625 g jars 43.9 24 0 82.9043.900.0020.73
Wimmers gute Semmelknödel 20 bags x 4 pieces 33.25 22 80 339.25204.0022.0084.81
Louisiana Fiery Hot Pepper Sauce 32 - 8 oz bottles 21.05 76 0 129.0576.000.0032.26
Louisiana Hot Spiced Okra 24 - 8 oz jars 17 4 100 145.00100.004.0036.25
Laughing Lumberjack Lager 24 - 12 oz bottles 14 52 0 90.0052.000.0022.50
Scottish Longbreads 10 boxes x 8 pieces 12.5 6 10 136.50108.006.0034.13
Gudbrandsdalsost 10 kg pkg. 36 26 0 72.0036.000.0018.00
Outback Lager 24 - 355 ml bottles 15 15 10 64.0024.0010.0016.00
Fløtemysost 10 - 500 g pkgs. 21.5 26 0 57.5026.000.0014.38
Mozzarella di Giovanni 24 - 200 g pkgs. 34.8 14 0 72.8034.800.0018.20
Röd Kaviar 24 - 150 g jars 15 101 0 140.00101.000.0035.00
Longlife Tofu 5 kg pkg. 10 4 20 39.0020.004.009.75
Rhönbräu Klosterbier 24 - 0.5 l bottles 7.75 125 0 156.75125.000.0039.19
Lakkalikööri 500 ml 18 57 0 575.00500.000.00143.75
Original Frankfurter grüne Soße 12 boxes 13 32 0 57.0032.000.0014.25
sdg 0.0000 0 0 0.000.000.000.00
ghgfhfgh 0.0000 0 0 0.000.000.000.00
Aniseed Syru 0.0000 0 0 0.000.000.000.00
gfdg 0.0000 0 0 0.000.000.000.00
1245 0.0000 0 0 0.000.000.000.00
111 0.0000 0 0 0.000.000.000.00
rtgretewrtwerr 0.0000 0 0 0.000.000.000.00
Col Total10873 Items2222.713,169.00 Units780.00 Units10,873.40 
Col Max5030263.5125100 
Col Min1000 
Col Avg141.2121.3730.477.50 
Col Count77104104104 

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

Send comments on this example.
Copyright 1998-2006 ASP-db