Microsoft Visual C# With ADO.NET: Aggregate Queries

Microsoft SQL Server is a powerful application that can be used in various scenarios. For example, a statistitian can use it to keep records and analyze the meaning of numbers stored in tables. To assist you with this, Transact-SQL provides many statistic-based functions, referred to as aggregate functions. They allow you to create particular views named aggregate queries.

Although you can create an aggregate query with all fields or any field(s) of a table or view, the purpose of the query is to summarize data. For a good summary query, you should select a column where the records hold categories of data. This means that the records in the resulting query have to be grouped by categories. To support this, the SQL provides the GROUP BY expression. It is added after the FROM clause. This is done as follows:

The new expression in this formula is GROUP BY. This indicates that you want to group some values from one or more columns. Of course, there are rules you must follow.

As stated already, the purpose of an aggregate query is to provide some statistics. Therefore, it is normal that you be interested only in the column(s) that hold(s) the desired statistics and avoid the columns that are irrelevant. As a result, if you select (only) the one column that holds the information you want, in the resulting list, each of its categories would display only once.

Transact-SQL and Statistics

Introduction to Aggregate Functions

Transact-SQL provides many built-in functions used to get statistics. These functions are used in various circumstances, depending on the nature of the column being investigated. This means that you should first decide what type of value you wand to get, then choose the appropriate function. To call the function in SQL code, start a SELECT statement and pass the column to the function. The minimum formula to follow is:

SELECT FunctionName(FieldName) FROM TableName;

The Number of Values (The Size of a Sample)

Probably the most basic piece of information you may want to get about a table or query is the number of records it has. In statistics, this is referred to as the number of samples. To help you get this information, Transact-SQL provides a function named Count. It counts the number of records in a column and produces the total. This function also counts NULL values. The syntax of the Count() function is:

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } ) RETURNS INT

This function takes one argument. The Count() function returns an int value. Here is an example:

Summary Query

If you are working on a large number of records, you can call the Count_Big() function. Its syntax is:

COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * ) RETURNS bigint

Both count functions allow you to pass the name of the column that has the values. By default, these functions would count only the records that have a known value, not including NULL values.

Practical Learning: Getting the Number of Records

In the ColumnHeader Collection Editor, click Add and create a column with the following characteristics:
(Name): colCount
Text: Number of Properties
TextAlign: Right
Width: 112
Click OK
Right-click the form and click View Code

Add the following code to the bottom section of the ShowProperties method juste before the closing curly bracket:

. . .

    using (SqlConnection scAltairRealtors =
        new SqlConnection("Data Source=(local);" +
                           "Database='AltairRealtors1';" +
                           "Integrated Security='SSPI';"))
    {
        SqlCommand cmdProperties =
            new SqlCommand("SELECT PropertyType AS Category, " +
                           "       COUNT(PropertyNumber) AS Count " +
                           "FROM Listings.Properties " +
                           "GROUP BY PropertyType;",
                           scAltairRealtors);

        scAltairRealtors.Open();
        cmdProperties.ExecuteNonQuery();

        SqlDataAdapter sdaProperties = new SqlDataAdapter(cmdProperties);
        DataSet dsProperties = new DataSet("PropertiesSet");

        sdaProperties.Fill(dsProperties);

        foreach (DataRow house in dsProperties.Tables[0].Rows)
        {
            ListViewItem lviProperty = new ListViewItem(house["Category"].ToString());

            lviProperty.SubItems.Add(house["Count"].ToString());
            lvwPropertiesStatistics.Items.Add(lviProperty);
        }
    }
}

Execute the application
Close the forms and return to your programming environment

The Minimum Value of a Series

If you have a list of values, you may want to get the lowest value. For example, in a list of houses of a real estate company with each property having a price, you may want to know which house is the cheapest. To let you get this information, Transact-SQL provides a function named MIN. Its syntax is:

DependsOnType MIN ( [ ALL | DISTINCT ] expression )

The return value of the MIN() function depends on the type of value that is passed to it. For example, if you pass a column that is number-based, the function returns the highest number. Here is an example:

USE DepartmentStore1;
GO

SELECT MIN(si.UnitPrice) N'Cheapest'
FROM Inventory.StoreItems si;
GO

If you pass a string-based column, the function returns the the last value in the alphabetical order. Here is an example:

USE rosh;
GO

SELECT MIN(stds.LastName) [First Student]
FROM Registration.Students stds;
GO

In the same way, you can pass a date/time-based column. Here is an example:

USE rosh;
GO

SELECT MIN(stds.DateOfBirth) "Youngest Student"
FROM Registration.Students stds;
GO

Be careful when passing a value to an aggregate function such as MIN(). For example, if the name of a column is processed by a function, the returned value would be used by the aggregate function. Consider the following call:

SELECT MIN(FORMAT(Studs.BirthDate, N'D')) [Earliest Birthdate]
FROM Studs;
GO

This would produce:

Minimum

Notice that the name Friday, as a string, is the one being processed by the MIN() function, instead of the actual date.

Practical Learning: Getting the Minimum of a Series

Right-click the bottom list view and click edit columns
In the ColumnHeader Collection Editor, click Add and create a column with the following characteristics:
(Name): colMinimum
Text: Cheapest Property
TextAlign: Right
Width: 100
Click OK
Right-click the form and click View Code

Change the bottom section of the ShowProperties method as follows:

. . .

    using (SqlConnection scAltairRealtors =
        new SqlConnection("Data Source=(local);" +
                           "Database='AltairRealtors2';" +
                           "Integrated Security='SSPI';"))
    {
        SqlCommand cmdProperties =
            new SqlCommand("SELECT PropertyType AS Category, " +
                           "       COUNT(PropertyNumber) AS Count, " +
                           "       MIN(MarketValue) AS Minimum " +
                           "FROM Listings.Properties " +
                           "GROUP BY PropertyType;",
                           scAltairRealtors);

        scAltairRealtors.Open();
        cmdProperties.ExecuteNonQuery();

        SqlDataAdapter sdaProperties = new SqlDataAdapter(cmdProperties);
        DataSet dsProperties = new DataSet("PropertiesSet");

        sdaProperties.Fill(dsProperties);

        foreach (DataRow house in dsProperties.Tables[0].Rows)
        {
            ListViewItem lviProperty = new ListViewItem(house["Category"].ToString());

            lviProperty.SubItems.Add(house["Count"].ToString());
            lviProperty.SubItems.Add(double.Parse(house["Minimum"].ToString()).ToString("C"));
            lvwPropertiesStatistics.Items.Add(lviProperty);
        }
    }
}

Execute the application
Close the forms and return to your programming environment

The Maximum Value of a Series

The opposite of the lowest is the highest value of a series. To assist you with getting this value, Transact-SQL provides the Max() function. Its function is:

DependsOnType MAX ( [ ALL | DISTINCT ] expression )

This function follows the same rules as its MIN() counterpart, but in reverse order (of the rules). Here is an example:

SELECT MAX(si.UnitPrice) N'Most Expensive'
FROM Inventory.StoreItems si;

Practical Learning: Getting the Highest Value of a Series

Right-click the bottom list view and click edit columns
In the ColumnHeader Collection Editor, click Add and create a column with the following characteristics:
(Name): colMaximum
Text: Most Expensive
TextAlign: Right
Width: 88
Click OK
Right-click the form and click View Code

Change the bottom section of the ShowProperties method as follows:

. . .

    using (SqlConnection scAltairRealtors =
        new SqlConnection("Data Source=(local);" +
                           "Database='AltairRealtors2';" +
                           "Integrated Security='SSPI';"))
    {
        SqlCommand cmdProperties =
            new SqlCommand("SELECT PropertyType AS Category, " +
                           "       COUNT(PropertyNumber) AS Count, " +
                           "       MIN(MarketValue) AS Minimum, " +
                           "       MAX(MarketValue) AS Maximum, " +
                           "FROM Listings.Properties " +
                           "GROUP BY PropertyType;",
                           scAltairRealtors);

        scAltairRealtors.Open();
        cmdProperties.ExecuteNonQuery();

        SqlDataAdapter sdaProperties = new SqlDataAdapter(cmdProperties);
        DataSet dsProperties = new DataSet("PropertiesSet");

        sdaProperties.Fill(dsProperties);

        foreach (DataRow house in dsProperties.Tables[0].Rows)
        {
            ListViewItem lviProperty = new ListViewItem(house["Category"].ToString());

            lviProperty.SubItems.Add(house["Count"].ToString());
            lviProperty.SubItems.Add(double.Parse(house["Minimum"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["Maximum"].ToString()).ToString("C"));
            lvwPropertiesStatistics.Items.Add(lviProperty);
        }
    }
}

Execute the application
Close the forms and return to your programming environment

The Sum of Values

The sum of the values of a series is gotten by adding all values. In algebra and statistics, it is represented as follows:

∑x

To let you calculate the sum of values of a certain column of a table, Transact-SQL provides a function named Sum. The syntax of the Sum() function is:

Number SUM ( [ ALL | DISTINCT ] expression )

Unlike the MIN() and the MAX() functions that can receive a column of almost any type, the column passed to the SUM() function must be number-based.

Practical Learning: Getting the Sum of Value

Right-click the bottom list view and click edit columns
In the ColumnHeader Collection Editor, click Add and create a column with the following characteristics:
(Name): colSum
Text: Estimated Total Assets
TextAlign: Right
Width: 120
Click OK
Right-click the form and click View Code

Change the bottom section of the ShowProperties method as follows:

. . .

    using (SqlConnection scAltairRealtors =
        new SqlConnection("Data Source=(local);" +
                           "Database='AltairRealtors2';" +
                           "Integrated Security='SSPI';"))
    {
        SqlCommand cmdProperties =
            new SqlCommand("SELECT PropertyType AS Category, " +
                           "       COUNT(PropertyNumber) AS Count, " +
                           "       MIN(MarketValue) AS Minimum, " +
                           "       MAX(MarketValue) AS Maximum, " +
                           "       SUM(MarketValue) AS Sum, " +
                           "FROM Listings.Properties " +
                           "GROUP BY PropertyType;",
                           scAltairRealtors);

        scAltairRealtors.Open();
        cmdProperties.ExecuteNonQuery();

        SqlDataAdapter sdaProperties = new SqlDataAdapter(cmdProperties);
        DataSet dsProperties = new DataSet("PropertiesSet");

        sdaProperties.Fill(dsProperties);

        foreach (DataRow house in dsProperties.Tables[0].Rows)
        {
            ListViewItem lviProperty = new ListViewItem(house["Category"].ToString());

            lviProperty.SubItems.Add(house["Count"].ToString());
            lviProperty.SubItems.Add(double.Parse(house["Minimum"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["Maximum"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["Sum"].ToString()).ToString("C"));
            lvwPropertiesStatistics.Items.Add(lviProperty);
        }
    }
}

Execute the application
Close the forms and return to your programming environment

The Mean

In algebra and statistics, the mean is the average of the numeric values of a series. To calculate it, you can divide the sum by the number of values of the series. It is calculated using the following formula:

From this formula:

x representes each value of the series (called a sample)
n represents the number (count) of values
x represents the mean of the x values
∑x represents the sum of x values

To support this operation, Transact-SQL provides the Avg function. Its syntax is:

AVG ( [ ALL | DISTINCT ] expression ) RETURNS Number

Practical Learning: Getting the Mean of Value

Right-click the bottom list view and click edit columns
In the ColumnHeader Collection Editor, click Add and create a column with the following characteristics:
(Name): colAverage
Text: Average Market Value
TextAlign: Right
Width: 120
Click OK
Right-click the form and click View Code

Change the bottom section of the ShowProperties method as follows:

. . .

    using (SqlConnection scAltairRealtors =
        new SqlConnection("Data Source=(local);" +
                           "Database='AltairRealtors2';" +
                           "Integrated Security='SSPI';"))
    {
        SqlCommand cmdProperties =
            new SqlCommand("SELECT PropertyType AS Category, " +
                           "       COUNT(PropertyNumber) AS Count, " +
                           "       MIN(MarketValue) AS Minimum, " +
                           "       MAX(MarketValue) AS Maximum, " +
                           "       SUM(MarketValue) AS Sum, " +
                           "       AVG(MarketValue) AS Average, " +
                           "FROM Listings.Properties " +
                           "GROUP BY PropertyType;",
                           scAltairRealtors);

        scAltairRealtors.Open();
        cmdProperties.ExecuteNonQuery();

        SqlDataAdapter sdaProperties = new SqlDataAdapter(cmdProperties);
        DataSet dsProperties = new DataSet("PropertiesSet");

        sdaProperties.Fill(dsProperties);

        foreach (DataRow house in dsProperties.Tables[0].Rows)
        {
            ListViewItem lviProperty = new ListViewItem(house["Category"].ToString());

            lviProperty.SubItems.Add(house["Count"].ToString());
            lviProperty.SubItems.Add(double.Parse(house["Minimum"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["Maximum"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["Sum"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["Average"].ToString()).ToString("C"));
            lvwPropertiesStatistics.Items.Add(lviProperty);
        }
    }
}

Execute the application
Close the forms and return to your programming environment

The Standard Deviation of a Series

Imagine you have a column with numeric values. You already know how to get the sum and the mean. The standard deviation is a value by which the elements vary (deviate) from the mean. The formula to calculate the standard deviation is:

Standard Deviation

From this formula:

x represents each value of the field (column)
n represents the number (count) of records
x represents the mean of the x records
∑ represents a sum

The above formula wants you to first calculate the mean. As an alternative, you can use a formula that does not require the mean. It is:

Standard Deviation

Instead of creating your own function, Transact-SQL can assist you. First there are two types of standard deviations. The sample standard deviation relates to a sample. To let you calculate it, Transact-SQL provides a function named StdDev. Its syntax is:

STDEV ( [ ALL | DISTINCT ] expression ) RETURNS float

The other standard deviation relates to a population. To help you calculate it, Transact-SQL provides the STDDEVP() function. Its syntax is:

STDEVP ( [ ALL | DISTINCT ] expression ) RETURNS float

Practical Learning: Getting the Standard Deviation

Right-click the bottom list view and click edit columns
In the ColumnHeader Collection Editor, click Add and create a column with the following characteristics:
(Name): colStandardDeviation
Text: Standard Deviation
TextAlign: Right
Width: 105
Click OK
Right-click the form and click View Code

Change the bottom section of the ShowProperties method as follows:

. . .

    using (SqlConnection scAltairRealtors =
        new SqlConnection("Data Source=(local);" +
                           "Database='AltairRealtors2';" +
                           "Integrated Security='SSPI';"))
    {
        SqlCommand cmdProperties =
            new SqlCommand("SELECT PropertyType AS Category, " +
                           "       COUNT(PropertyNumber) AS Count, " +
                           "       MIN(MarketValue) AS Minimum, " +
                           "       MAX(MarketValue) AS Maximum, " +
                           "       SUM(MarketValue) AS Sum, " +
                           "       AVG(MarketValue) AS Average, " +
                           "       STDEV(MarketValue) AS StandardDeviation " +
                           "FROM Listings.Properties " +
                           "GROUP BY PropertyType;",
                           scAltairRealtors);

        scAltairRealtors.Open();
        cmdProperties.ExecuteNonQuery();

        SqlDataAdapter sdaProperties = new SqlDataAdapter(cmdProperties);
        DataSet dsProperties = new DataSet("PropertiesSet");

        sdaProperties.Fill(dsProperties);

        foreach (DataRow house in dsProperties.Tables[0].Rows)
        {
            ListViewItem lviProperty = new ListViewItem(house["Category"].ToString());

            lviProperty.SubItems.Add(house["Count"].ToString());
            lviProperty.SubItems.Add(double.Parse(house["Minimum"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["Maximum"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["Sum"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["Average"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["StandardDeviation"].ToString()).ToString("C"));
            lvwPropertiesStatistics.Items.Add(lviProperty);
        }
    }
}

Execute the application
Close the forms and return to your programming environment

The Variance of a Series

The variance is the square of the standard deviation. This means that, to calculate it, you can just square the value of a standard deviation.

As seen with the standard deviation, there are two types of variances. A sample variance relates to a sample. To help you calculate a sample variance of records, Transact-SQL provides VAR function. Its syntax is:

VAR ( [ ALL | DISTINCT ] expression ) RETURNS float

The function used to calculate a population variance is VARP and its syntax is:

VARP ( [ ALL | DISTINCT ] expression ) RETURNS float

Practical Learning: Getting the Sample Variance

Right-click the bottom list view and click edit columns
In the ColumnHeader Collection Editor, click Add and create a column with the following characteristics:
(Name): colVariance
Text: Market Variance
TextAlign: Right
Width: 90
Click OK
Right-click the form and click View Code

Change the bottom section of the ShowProperties method as follows:

internal void ShowProperties()
{
    using (SqlConnection scAltairRealtors =
        new SqlConnection("Data Source=(local);" +
                           "Database='AltairRealtors2';" +
                           "Integrated Security='SSPI';"))
    {
        SqlCommand cmdProperties =
            new SqlCommand("SELECT props.PropertyNumber, " +
                           "       props.PropertyType, " +
                           "       props.City, " +
                           "       props.Bedrooms, " +
                           "       props.Bathrooms, " +
                           "       props.FinishedBasement, " +
                           "       props.YearBuilt, " +
                           "       props.Condition, " +
                           "       props.MarketValue, " +
                           "       props.SaleStatus " +
                           "FROM Listings.Properties props;",
                           scAltairRealtors);
        cmdProperties.CommandType = CommandType.Text;

        SqlDataAdapter sdaProperties = new SqlDataAdapter();
        scAltairRealtors.Open();

        DataSet dsProperties = new DataSet("PropertiesSet");
        sdaProperties.SelectCommand = cmdProperties;
        sdaProperties.Fill(dsProperties);
        cmdProperties.ExecuteNonQuery();

        lvwProperties.Items.Clear();
        lvwPropertiesStatistics.Items.Clear();

        foreach (DataRow house in dsProperties.Tables[0].Rows)
        {
            ListViewItem lviProperty = null;

            if (house["PropertyType"].ToString() == "Condominium")
                lviProperty = new ListViewItem(house["PropertyNumber"].ToString(), lvwProperties.Groups[0]);
            else if (house["PropertyType"].ToString() == "Townhouse")
                lviProperty = new ListViewItem(house["PropertyNumber"].ToString(), lvwProperties.Groups[1]);
            else
                lviProperty = new ListViewItem(house["PropertyNumber"].ToString(), lvwProperties.Groups[2]);

            lviProperty.SubItems.Add(house["City"].ToString());
            lviProperty.SubItems.Add(house["Bedrooms"].ToString());
            lviProperty.SubItems.Add(double.Parse(house["Bathrooms"].ToString()).ToString("F"));
            lviProperty.SubItems.Add(house["FinishedBasement"].ToString());
            lviProperty.SubItems.Add(house["Condition"].ToString());
            lviProperty.SubItems.Add(house["YearBuilt"].ToString());
            lviProperty.SubItems.Add(house["MarketValue"].ToString());
            lviProperty.SubItems.Add(house["SaleStatus"].ToString());
            lvwProperties.Items.Add(lviProperty);
        }

        pbxProperty.Image = Image.FromFile("C:\\Altair Realtors\\default.jpg");
    }

    using (SqlConnection scAltairRealtors =
        new SqlConnection("Data Source=(local);" +
                           "Database='AltairRealtors2';" +
                           "Integrated Security='SSPI';"))
    {
        SqlCommand cmdProperties = new SqlCommand("SELECT props.PropertyID [Prop ID], " +
                                                  "       props.PropertyNumber [Property #], " +
                                                  "       props.PropertyType   [Type], " +
                                                  "       props.[Address], " +
                                                  "       props.City, " +
                                                  "       props.[State], " +
                                                  "       props.ZIPCode [ZIP Code], " +
                                                  "       props.Bedrooms Beds, " +
                                                  "       props.Bathrooms Baths, " +
                                                  "       props.Stories, " +
                                                  "       props.FinishedBasement [Finished Basement?], " +
                                                  "       props.IndoorGarage [Indoor Garage], " +
                                                  "       props.YearBuilt [Year Built], " +
                                                  "       props.Condition, " +
                                                  "       props.MarketValue [Market Value], " +
                                                  "       props.SaleStatus " +
                                                  "FROM Listings.Properties props " +
                                                  "ORDER BY props.PropertyID;", scAltairRealtors);
        cmdProperties.CommandType = CommandType.Text;

        SqlDataAdapter sdaProperties = new SqlDataAdapter();
        scAltairRealtors.Open();

        DataSet dsProperties = new DataSet("PropertiesSet");
        sdaProperties.SelectCommand = cmdProperties;
        sdaProperties.Fill(dsProperties);
        dgvProperties.DataSource = dsProperties.Tables[0];
        cmdProperties.ExecuteNonQuery();
    }

    using (SqlConnection scAltairRealtors =
        new SqlConnection("Data Source=(local);" +
                           "Database='AltairRealtors2';" +
                           "Integrated Security='SSPI';"))
    {
        SqlCommand cmdProperties =
            new SqlCommand("SELECT PropertyType AS Category, " +
                           "       COUNT(PropertyNumber) AS Count, " +
                           "       MIN(MarketValue) AS Minimum, " +
                           "       MAX(MarketValue) AS Maximum, " +
                           "       SUM(MarketValue) AS Sum, " +
                           "       AVG(MarketValue) AS Average, " +
                           "       STDEV(MarketValue) AS StandardDeviation, " +
                           "       VAR(MarketValue) AS Variance " +
                           "FROM Listings.Properties " +
                           "GROUP BY PropertyType;",
                           scAltairRealtors);

        scAltairRealtors.Open();
        cmdProperties.ExecuteNonQuery();

        SqlDataAdapter sdaProperties = new SqlDataAdapter(cmdProperties);
        DataSet dsProperties = new DataSet("PropertiesSet");

        sdaProperties.Fill(dsProperties);

        foreach (DataRow house in dsProperties.Tables[0].Rows)
        {
            ListViewItem lviProperty = new ListViewItem(house["Category"].ToString());

            lviProperty.SubItems.Add(house["Count"].ToString());
            lviProperty.SubItems.Add(double.Parse(house["Minimum"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["Maximum"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["Sum"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["Average"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["StandardDeviation"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["Variance"].ToString()).ToString("C"));
            lvwPropertiesStatistics.Items.Add(lviProperty);
        }
    }
}

private void AltairRealtors_Load(object sender, EventArgs e)
{
    ShowProperties();
}

Execute the application
Close the forms and return to your programming environment

Intermediate Aggregate Operations

Using a Condition

All of the aggregate queries we have used so far involved all the records of a table or view. In some cases, you may want to restrict the records to consider. As you may know by now, this is done by adding a condition to a SQL statement.

To add a condition to an aggregate query, instead of WHERE, you use the HAVING keyword. Here is an example:

using (SqlConnection scAltairRealtors =
        new SqlConnection("Data Source=(local);" +
                           "Database='AltairRealtors2';" +
                           "Integrated Security='SSPI';"))
{
    SqlCommand cmdProperties =
        new SqlCommand("SELECT PropertyType AS Category, " +
                       "       COUNT(PropertyNumber) AS Count, " +
                       "       MIN(MarketValue) AS Minimum, " +
                       "       MAX(MarketValue) AS Maximum, " +
                       "       SUM(MarketValue) AS Sum, " +
                       "       AVG(MarketValue) AS Average, " +
                       "       STDEV(MarketValue) AS StandardDeviation, " +
                       "       VAR(MarketValue) AS Variance " +
                       "FROM Listings.Properties " +
                       "GROUP BY PropertyType " +
                       "HAVING PropertyType = 'Single Family';",
                       scAltairRealtors);

    scAltairRealtors.Open();
    cmdProperties.ExecuteNonQuery();

    SqlDataAdapter sdaProperties = new SqlDataAdapter(cmdProperties);
    DataSet dsProperties = new DataSet("PropertiesSet");

    sdaProperties.Fill(dsProperties);
}

Practical Learning: Setting a Condition

Add a label to the bottom section of the form and change its characteristics as follows:
Anchor: Bottom, Left
Text: Show Statistics for Only:
Add a combo box to the form and change its characteristics as follows:
(Name): cbxPropertiesTypes
Anchor: Bottom, Left
DropDownStyle: DropDownList
Items: Townhouses
Condominiums
Single Families

Double-click the combo box and implement the event as follows:

private void cbxPropertiesTypes_SelectedIndexChanged(object sender, EventArgs e)
{
    string strPropertyType = "";

    if (cbxPropertiesTypes.Text == "Condominiums")
        strPropertyType = "Townhouse";
    else if (cbxPropertiesTypes.Text == "Condominiums")
        strPropertyType = "Condominium";
    else // if (cbxPropertiesTypes.Text == "Single Families")
        strPropertyType = "Single Family";

    lvwPropertiesStatistics.Items.Clear();

    using (SqlConnection scAltairRealtors =
            new SqlConnection("Data Source=(local);" +
                               "Database='AltairRealtors2';" +
                               "Integrated Security='SSPI';"))
    {
        SqlCommand cmdProperties =
            new SqlCommand("SELECT PropertyType AS Category, " +
                           "       COUNT(PropertyNumber) AS Count, " +
                           "       MIN(MarketValue) AS Minimum, " +
                           "       MAX(MarketValue) AS Maximum, " +
                           "       SUM(MarketValue) AS Sum, " +
                           "       AVG(MarketValue) AS Average, " +
                           "       STDEV(MarketValue) AS StandardDeviation, " +
                           "       VAR(MarketValue) AS Variance " +
                           "FROM Listings.Properties " +
                           "GROUP BY PropertyType " +
                           "HAVING PropertyType = '" + strPropertyType + "';",
                           scAltairRealtors);

        scAltairRealtors.Open();
        cmdProperties.ExecuteNonQuery();

        SqlDataAdapter sdaProperties = new SqlDataAdapter(cmdProperties);
        DataSet dsProperties = new DataSet("PropertiesSet");

        sdaProperties.Fill(dsProperties);

        foreach (DataRow house in dsProperties.Tables[0].Rows)
        {
            ListViewItem lviProperty = new ListViewItem(house["Category"].ToString());

            lviProperty.SubItems.Add(house["Count"].ToString());
            lviProperty.SubItems.Add(double.Parse(house["Minimum"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["Maximum"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["Sum"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["Average"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["StandardDeviation"].ToString()).ToString("C"));
            lviProperty.SubItems.Add(double.Parse(house["Variance"].ToString()).ToString("C"));
            lvwPropertiesStatistics.Items.Add(lviProperty);
        }
    }
}

Execute the application
Select an option, such as Single Family, from the combo box
Close the forms and return to your programming environment

Computing

Imagine you have a table that has one or more fields with numeric values and you use a SELECT statement to select some of those columns. At the end the statement, you can ask the database engine to perform a calculation using one or more of the aggregate functions and show the result(s). To do this, you use the COMPUTE keyword in a formula as follows:

[ COMPUTE 
    { { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM } 
  ( expression ) } [ ,...n ] 
    [ BY expression [ ,...n ] ] 
]

As you can see, you start with COMPUTE followed by the desired function, which uses parentheses. In the parentheses, include the name of the column that holds the numeric values.

Header	Name
Condominium	lvgCondominium
Townhouse	lvgTownhouse
Single Family	lvgSingleFamily