This tutorial shows in a simple Cω program how to use SQL aggregate functions.
To run this tutorial, you may use the following project and source files:
These files are located in the \samples\SQL\Aggregates subdirectory under the path where you installed Cω, which by default is C:\Program Files\Microsoft Research\Comega.
To run this sample, you need to have a SQL configuration file in the same directory as the Select.exe you make in Visual Studio. Here are the contents of the version of this file that Cω provided you as a starting point:
<configuration>
<appSettings>
<add key="Northwind" value="Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI" />
</appSettings>
</configuration>
Verify that this is the correct SQL connection string for connecting to the Northwind database on your SQL Server 2000 installation. If necessary, you can modify the value attribute in the <add> element
When you have verified it is correct, press F5 in Visual Studio to make and run the sample.
An aggregate is a function that reduces a series of values into a single value. In SQL, the list of aggregate function you might use includes those in the following table:
| Aggregate | Description |
|---|---|
| Count | The total number of values |
| Min | The minimum value |
| Max | The maximum value |
| Sum | The sum of all values |
| Avg | The average of all values |
| Stdev | The standard deviation |
The following is a complete Cω program that demonstrates how to use SQL aggregates.
using System;
using System.Data.SqlTypes;
using System.Query;
using Northwind;
public class Test {
static void Main() {
Console.WriteLine("SQL Aggregates");
Console.WriteLine("--------------");
// Use SQL Aggregates in a select expression
r = select singleton
Count(Freight) as cnt,
Min(Freight) as min,
Max(Freight) as max,
Sum(Freight) as sum,
Avg(Freight) as avg,
Stdev(Freight) as std
from o in DB.Orders
where o.CustomerID == "ALFKI";
Console.WriteLine("count : {0}", r.cnt);
Console.WriteLine("min : {0}", r.min);
Console.WriteLine("max : {0}", r.max);
Console.WriteLine("sum : {0}", r.sum);
Console.WriteLine("avg : {0}", r.avg);
Console.WriteLine("stdev : {0}", r.std);
// Use SQL Aggregates in having and order by clauses too
Console.WriteLine("\nCustomers with freight costs > $1000.00");
Console.WriteLine("ID Freight");
Console.WriteLine("----- -----------");
foreach( row in select cid, Sum(Freight)
from c in DB.Customers
left join o in DB.Orders on c.CustomerID == o.CustomerID
group by c.CustomerID as cid
having Sum(o.Freight) > 1000.0M
order by Sum(Freight)
) {
Console.WriteLine("{0,5} {1,8}", row.cid, row[1]);
}
// Use SQL Aggregates everywhere!
int[] series = {1, 2, 3, 4, 5, 6};
Console.WriteLine("\nSum of the series: {0}", Sum(series));
Console.Write("\nPress ENTER to continue...");
Console.ReadLine();
}
}
SQL Aggregates -------------- count : 6 min : 1.2100 max : 69.5300 sum : 225.5800 avg : 37.5966 stdev : 25.1119928852066 Customers with freight costs > $1000.00 ID Freight ----- ----------- RICSU 1001.2900 LEHMS 1017.0300 GREAL 1087.6100 PICCO 1186.1100 HILAA 1259.1600 WHITC 1353.0600 BONAP 1357.8700 MEREP 1394.2200 FRANK 1403.4400 BERGS 1559.5200 FOLKO 1678.0800 QUEEN 1982.7000 RATTC 2134.2100 HUNGO 2755.2400 QUICK 5605.6300 ERNSH 6205.3900 SAVEA 6683.7000 Sum of the series: 21 Press ENTER to continue...