Cω - Samples

Aggregate Functions Tutorial

This tutorial shows in a simple Cω program how to use SQL aggregate functions.

Sample Files

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:

    <add key="Northwind" value="Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI" />

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.

Further Reading


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");

    // 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...");


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...