This tutorial shows in a simple Cω program how to use the GROUP BY clause within a SQL Select expression.
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.
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();
}
}
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...