Cω - Samples

Group By Clause Tutorial

This tutorial shows in a simple Cω program how to use the GROUP BY clause within a SQL Select expression.

Sample Files

To run this tutorial, you may use the following project and source files:

These files are located in the \samples\SQL\Grouping 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.

Further Reading

Example

The following is a complete Cω program that demonstrates how to use the GROUP BY clause within a SQL Select expression.

using System;
using System.Data.SqlTypes;
using System.Query;
using Northwind;

public class Test {
  static void Main() {
    results =
      select ProductName
        from od in DB.OrderDetails inner join p in DB.Products on od.ProductID == p.ProductID
        where od.Quantity > p.UnitsInStock
        group by p.ProductName
        order by ProductName;

    Console.WriteLine("Products that have orders exceeding stock on hand.\n");
    foreach( row in results ) {
      Console.WriteLine("{0}", row.ProductName);
    }

    Console.Write("\nPress ENTER to continue...");
    Console.ReadLine();
  }
}

Output

Products that have orders exceeding stock on hand.

Alice Mutton
Aniseed Syrup
Camembert Pierrot
Carnarvon Tigers
Chai
Chang
Chartreuse verte
Chef Anton's Gumbo Mix
Chocolade
Côte de Blaye
Escargots de Bourgogne
Filo Mix
Flotemysost
Genen Shouyu
Gnocchi di nonna Alice
Gorgonzola Telino
Gravad lax
Guaraná Fantástica
Gudbrandsdalsost
Gula Malacca
Gumbär Gummibärchen
Ikura
Ipoh Coffee
Jack's New England Clam Chowder
Konbu
Lakkalikööri
Longlife Tofu
Louisiana Fiery Hot Pepper Sauce
Louisiana Hot Spiced Okra
Manjimup Dried Apples
Mascarpone Fabioli
Maxilaku
Mishi Kobe Niku
Mozzarella di Giovanni
Nord-Ost Matjeshering
Northwoods Cranberry Sauce
Original Frankfurter grüne Soße
Outback Lager
Pâté chinois
Pavlova
Perth Pasties
Queso Cabrales
Queso Manchego La Pastora
Raclette Courdavault
Ravioli Angelo
Rogede sild
Rössle Sauerkraut
Schoggi Schokolade
Scottish Longbreads
Singaporean Hokkien Fried Mee
Sir Rodney's Marmalade
Sir Rodney's Scones
Sirop d'érable
Steeleye Stout
Tarte au sucre
Teatime Chocolate Biscuits
Thüringer Rostbratwurst
Tofu
Tourtière
Tunnbröd
Uncle Bob's Organic Dried Pears
Vegie-spread
Wimmers gute Semmelknödel
Zaanse koeken

Press ENTER to continue...