Home

Aggregate Queries

   

Introduction to Aggregate Queries

 

Overview

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.

Consider the following table named Employees:

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    DataGridView dgvIdentifications;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        dgvIdentifications = new DataGridView();
        dgvIdentifications.Location = new Point(12, 12);
        dgvIdentifications.Size = new System.Drawing.Size(270, 250);

        Text = "Exercise";
        Controls.Add(dgvIdentifications);
        Load += new EventHandler(ExerciseLoad);

        dgvIdentifications.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                                    AnchorStyles.Right | AnchorStyles.Bottom;
    }

    void ExerciseLoad(object sender, EventArgs e)
    {
        SqlConnectionStringBuilder csbExercise = new SqlConnectionStringBuilder();

        csbExercise.DataSource = "(local)";
        csbExercise.InitialCatalog = "Exercise1";
        csbExercise.IntegratedSecurity = true;

        using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
        {
            SqlCommand cmdEmployees =
                new SqlCommand("CREATE SCHEMA Personnel;", cntExercise);
            cntExercise.Open();
            cmdEmployees.ExecuteNonQuery();
        }

        using (SqlConnection cntExercise =
            new SqlConnection("Data Source='(local)';" +
                              "Database='Exercise1';" +
                              "Integrated Security='SSPI';"))
        {
            SqlCommand cmdEmployees =
                new SqlCommand("CREATE TABLE Personnel.Employees(" +
                               "EmployeeNumber nchar(6) not null primary key, " +
                               "FirstName nvarchar(20), MiddleName nvarchar(20), " +
                               "LastName nvarchar(20), " +
                               "HourlySalary smallmoney, Status nvarchar(40));" +
                               "INSERT INTO Personnel.Employees " +
                               "VALUES(N'862804', N'Christopher', NULL, N'Larsen', 14.50, N'Full Time'), " +
                               "      (N'293747', N'Henry', N'Donald', N'Jonathan', 12.85, N'Full Time'), " +
                               "      (N'847597', N'Chistine', NULL, N'Garrison', 24.05, N'Part Time'), " +
                               "      (N'979558', N'Peter', NULL, N'Horries', NULL, NULL), " +
                               "      (N'385807', N'Lance', N'James', N'Seagal', 16.95, N'Full Time'), " +
                               "      (N'927405', N'Paula', N'Roberta', N'Ortez', NULL, N'Full Time'), " +
                               "      (N'790875', N'Paul', NULL, N'Swanson', 10.90, NULL), " +
                               "      (N'384096', N'Kristopher', N'Jude', N'Michaels', 12.85, N'Part Time'), " +
                               "      (N'385968', N'Jennifer', NULL, N'Sanders', 15.00, N'Part Time'), " +
                               "      (N'380696', N'David', N'Peter', N'Monahan', 13.05, N'Full Time');",
                               cntExercise);
            cntExercise.Open();
            cmdEmployees.ExecuteNonQuery();

            MessageBox.Show("A table named \"Employees\" has been created in the Personnel schema.",
                            "Exercise",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }

        using (SqlConnection cntExercise = new SqlConnection(csbExercise.ConnectionString))
        {
            SqlCommand cmdEmployees = new SqlCommand(
                    "SELECT ALL * FROM Personnel.Employees;",
                    cntExercise);

            cntExercise.Open();
            cmdEmployees.ExecuteNonQuery();

            SqlDataAdapter sdaEmployees = new SqlDataAdapter(cmdEmployees);
            DataSet dsEmployees = new DataSet("EmployeesSet");

            sdaEmployees.Fill(dsEmployees);
            dgvIdentifications.DataSource = dsEmployees.Tables[0];
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

Aggregate Queries

Practical LearningPractical Learning: Introducing Aggregate Queries

  1. Start Microsoft Visual Studio
  2. To start a new applicaiton, on the main menu, click FILE -> New Project...
  3. In the middle list, click Windows Forms Application and set the Name to AltairRealtors1
  4. Click OK
  5. In the Solution Explorer, right-click Form1.cs and click Rename
  6. Type AltairRealtors.cs and press Enter
  7. Double-click the middle of the form and change the file as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.IO;
    using System.Data.SqlClient;
    
    namespace AltairRealtors1
    {
        public partial class AltairRealtors : Form
        {
            public AltairRealtors()
            {
                InitializeComponent();
            }
    
            internal void CreateDatabase()
            {
                SqlConnection scAltairRealtors = null;
                SqlCommand cmdAltairRealtors = null;
    
                using (scAltairRealtors =
                    new SqlConnection("Data Source=(local);" +
                                       "Integrated Security='SSPI';"))
                {
                    cmdAltairRealtors =
                        new SqlCommand("IF EXISTS (" +
                                       "SELECT name " +
                                       "FROM sys.databases " +
                                       "WHERE name = N'AltairRealtors1')" +
                                       "DROP DATABASE AltairRealtors1; " +
                                       "CREATE DATABASE AltairRealtors1;", scAltairRealtors);
                    scAltairRealtors.Open();
                    cmdAltairRealtors.ExecuteNonQuery();
    
                    MessageBox.Show("The AltairRealtors1 database has been created.",
                                    "Altair Realtors",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (scAltairRealtors =
                    new SqlConnection("Data Source=(local);" +
                                       "Database='AltairRealtors1';" +
                                       "Integrated Security='SSPI';"))
                {
                    cmdAltairRealtors =
                        new SqlCommand("CREATE SCHEMA Listings;", scAltairRealtors);
                    scAltairRealtors.Open();
                    cmdAltairRealtors.ExecuteNonQuery();
    
                    MessageBox.Show("A new schema named Listings has been created.",
                                    "Altair Realtors",
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
    
                using (scAltairRealtors =
                    new SqlConnection("Data Source=(local);" +
                                       "Database='AltairRealtors1';" +
                                       "Integrated Security='SSPI';"))
                {
                    cmdAltairRealtors = new SqlCommand(
                        "CREATE TABLE Listings.Properties(" +
                        "PropertyID int identity(1, 1), PropertyNumber nvarchar(10) not null, PropertyType nvarchar(40), " +
                        "[Address] nvarchar(100), City nvarchar(50), [State] nchar(2), " +
                        "ZIPCode nvarchar(12), Bedrooms smallint, Bathrooms float, " +
                        "Stories smallint, FinishedBasement bit, IndoorGarage bit, " +
                        "YearBuilt smallint, Condition nvarchar(40), " +
                        "MarketValue money, SaleStatus nvarchar(50), " +
                        "Constraint PK_Properties Primary Key(PropertyNumber));",
                        scAltairRealtors);
                    scAltairRealtors.Open();
                    cmdAltairRealtors.ExecuteNonQuery();
    	    }
    
                MessageBox.Show("The Properties table has been created.",
                                "Altair Realtors",
                                MessageBoxButtons.OK, MessageBoxIcon.Information); 
    	}
    	
            private void AltairRealtors_Load(object sender, EventArgs e)
            {
                CreateDatabase();
            }
        }
    }
  8. Press Ctrl + F5 to execute
  9. Click OK on each message box
  10. Close the form and return to your programming environment
  11. To create a dialog box, on the main menu, click PROJECT -> Add Windows Form...
  12. Set the name to NewRealEstateProperty and click Add
  13. In the Dialogs section of the Toolbox, click OpenFileDialog
  14. Click the form
  15. In the Properties window, change its characteristics as follows:
    (Name):       dlgPicture
    DefaultExt:  jpg
    Filter:          JPEG Files (*.jpg,*.jpeg)|*.jpg
    Title:           Select House Picture
  16. Design the form as follows:
     
    Altair Realtors - New Real Estate Property
    Control (Name) DropDownStyle Text Items Modifiers Other Properties
    Label Label     Property #:      
    TextBox Text Box txtPropertyNumber       Public  
    Label Label     Property Type:      
    ComboBox Combo Box cbxPropertiesTypes DropDownList   Unknown
    Townhouse
    Condominium
    Single Family
    Public  
    Label Label     Address:      
    TextBox Text Box txtAddress       Public  
    Label Label     City:      
    TextBox Text Box txtCity       Public  
    Label Label     State/Province:      
    ComboBox Combo Box cbxStates DropDownList   AB, AL, AK, AZ, AR, BC, CA, CO, CT, DE, DC, FL, GA, HI, ID, IL, IN, IA, KS, KY, LA, ME, MD, MA, MI, MN, MS, MO, MT, NB, NE, NV, NH, NJ, NL, NM, NS, NY, NC, ND, OH, OK, ON, OR, PA, PE, QC, RI, SC, SD, SK, TN, TX, UT, VT, VA, WA, WV, WI, WY Public  
    Label Label     ZIP/Postal Code:      
    TextBox Text Box txtZIPCode       Public  
    Label Label     Stories:      
    TextBox Text Box txtStories       Public  
    Label Label     Bedrooms:      
    TextBox Text Box txtBedrooms   0   Public  
    Label Label     Bathrooms:      
    TextBox Text Box txtBathrooms   0.00   Public  
    CheckBox Check Box chkFinishedBasement   Finished Basement      
    CheckBox Check Box chkIndoorGarage   Indoor Garage      
    Label Label     Year Built:      
    TextBox Text Box txtYearBuilt       Public  
    Label Label     Condition:      
    ComboBox Combo Box cbxConditions DropDownList    Unknown
    Excellent
    Good Shape
    Needs Fixing
    Public  
    Label Label     Market Value:      
    TextBox Text Box txtMarketValue   0.00   Public  
    Label Label     Sale Status:      
    ComboBox Combo Box cbxSalesStatus DropDownList   Unknown
    Available
    Sold
    Public  
    Button Button btnPicture   Picture...      
    PictureBox Picture Box pbxProperty         BorderStyle: FixedSingle
    SizeMode: Zoom
    Button Button btnOK   OK     DialogResult: OK
    Button Button btnCancel   Cancel     DialogResult: Cancel
    Form
    FormBorderStyle: FixedDialog
    Text: Altair Realtors - New Real Estate Property
    StartPosition: CenterScreen
    AcceptButton: btnOK
    CancelButton: btnCancel
    MaximizeBox: False
    MinimizeBox: False
    ShowInTaskBar: False
  17. Double-click an unoccupied area of the dialog box
  18. Return to the form and double-click the Picture button
  19. Change the document as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.IO;
    
    namespace AltairRealtors1
    {
        public partial class NewRealEstateProperty : Form
        {
            public bool bPictureChanged;
            public string strPictureFile;
    
            public RealEstateProperty()
            {
                InitializeComponent();
            }
    
            private void NewRealEstateProperty_Load(object sender, EventArgs e)
            {
                bPictureChanged = false;
                Directory.CreateDirectory("C:\\Altair Realtors");
                strPictureFile = "C:\\Altair Realtors\\default.jpg";
            }
    
            private void btnPicture_Click(object sender, EventArgs e)
            {
                if (dlgPicture.ShowDialog() == DialogResult.OK)
                {
                    pbxProperty.Image = Image.FromFile(dlgPicture.FileName);
                    strPictureFile = dlgPicture.FileName;
                    bPictureChanged = true;
                }
            }
        }
    }
  20. To create another dialog box, on the main menu, click PROJECT -> Add Windows Form...
  21. Set the name to PropertyEditor and click Add
  22. Change the size of the form to be the same size as the New Real Estate Property form
  23. Add a button to the form and change its design as follows:
     
    Altair Realtors - Properties Review
     
    Control (Name) Text TextAlign
    Button Button btnFind Find  
  24. Double-click the Picture button
  25. Return to the form and double-click the Find button
  26. Change the file as follows:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.IO;
    using System.IO;
    using System.Data.SqlClient;
    
    namespace AltairRealtors1
    {
        public partial class PropertyEditor : Form
        {
            public bool bPictureChanged;
            public string strPictureFile;
    
            public PropertyEditor()
            {
                InitializeComponent();
            }
    
            private void btnPicture_Click(object sender, EventArgs e)
            {
                if (dlgPicture.ShowDialog() == DialogResult.OK)
                {
                    pbxProperty.Image = Image.FromFile(dlgPicture.FileName);
                    strPictureFile = dlgPicture.FileName;
                    bPictureChanged = true;
                }
            }
    
            private void btnFind_Click(object sender, EventArgs e)
            {
                using (SqlConnection scAltairRealtors =
                        new SqlConnection("Data Source=(local);" +
                                           "Database='AltairRealtors1';" +
                                           "Integrated Security='SSPI';"))
                {
                    SqlCommand cmdProperties =
                        new SqlCommand("SELECT PropertyNumber, " +
                                       "       PropertyType, " +
                                       "       [Address], " +
                                       "       City, " +
                                       "	   [State], " +
                                       "       ZIPCode, " +
                                       "       Bedrooms, " +
                                       "       Bathrooms, " +
                                       "       Stories, " +
                                       "       FinishedBasement, " +
                                       "       IndoorGarage, " +
                                       "       YearBuilt, " +
                                       "       Condition, " +
                                       "       MarketValue, " +
                                       "       SaleStatus " +
                                       "FROM Listings.Properties " +
                                       "WHERE PropertyNumber = '" + txtPropertyNumber.Text + "';",
                                       scAltairRealtors);
    
                    cmdProperties.CommandType = CommandType.Text;
    
                    SqlDataAdapter sdaProperties = new SqlDataAdapter();
                    scAltairRealtors.Open();
    
                    DataSet dsProperties = new DataSet("PropertiesSet");
                    sdaProperties.SelectCommand = cmdProperties;
    
                    sdaProperties.Fill(dsProperties);
                    cmdProperties.ExecuteNonQuery();
    
                    foreach (DataRow house in dsProperties.Tables[0].Rows)
                    {
                        cbxPropertiesTypes.Text = house["PropertyType"].ToString();
                        txtAddress.Text = house["Address"].ToString();
                        txtCity.Text = house["City"].ToString();
                        cbxStates.Text = house["State"].ToString();
                        txtZIPCode.Text = house["ZIPCode"].ToString();
                        txtBedrooms.Text = house["Bedrooms"].ToString();
                        txtBathrooms.Text = house["Bathrooms"].ToString();
                        chkFinishedBasement.Checked = bool.Parse(house["FinishedBasement"].ToString());
                        chkIndoorGarage.Checked = bool.Parse(house["IndoorGarage"].ToString());
                        txtStories.Text = house["Stories"].ToString();
                        txtYearBuilt.Text = house["YearBuilt"].ToString();
                        cbxConditions.Text = house["Condition"].ToString();
                        txtMarketValue.Text = house["MarketValue"].ToString();
                        cbxSalesStatus.Text = house["SaleStatus"].ToString();
                    }
                }
    
                // We need to locate the picture associated
                string strDirectory = "C:\\Altair Realtors";
                // First locate the folder where the pictures records are located
                DirectoryInfo dirProperties = new DirectoryInfo(strDirectory);
                // Get a list of (only) the picture files
                FileInfo[] PictureFiles = dirProperties.GetFiles("*.jpg");
    
                // At this time, we don't know if the picture exists
                bool pictureExists = false;
    
                // Look for a file that holds the same name as the property number
                foreach (FileInfo fle in PictureFiles)
                {
                    // Get the name of the file without its extension
                    string fwe = Path.GetFileNameWithoutExtension(fle.FullName);
    
                    // If you find a picture that has the same name as the property number
                    if (fwe == txtPropertyNumber.Text)
                    {
                        // Display that picture
                        pbxProperty.Image = Image.FromFile(strDirectory + "\\" + txtPropertyNumber.Text + ".jpg");
                        pictureExists = true;
                    }
    
                    // If there is no picture that has the same name as the 
                    // property number, display the default picture
                    if (pictureExists == false)
                        pbxProperty.Image = Image.FromFile("C:\\Altair Realtors\\default.jpg");
                }
            }
        }
    }
  27. Display the Altair Realtors form (the first form)
  28. Add a tab control to the form and set its Anchor property to Top, Bottom, Left, Right
  29. Add a list view to the first tab page
  30. Right-click the list view and click Edit Groups...
  31. Create the groups as follows:
     
    Header Name
    Condominium lvgCondominium
    Townhouse lvgTownhouse
    Single Family lvgSingleFamily
  32. Click OK
  33. Design the form as follows:
     
    Altair Realtors - Properties Listing
    Control (Name) Text Anchor
    Tab Page     Properties Summary  
    List View List View lvwProperties   Top, Bottom, Left
       
    (Name) Text TextAlign Width
    colPropertyNumber Property #   62
    colCity City   100
    colBedrooms Bedrooms Right 40
    colBathrooms Bathrooms Right 40
    colFinishedBasement FinishedBasement Center 110
    colCondition Condition   80
    colYearBuilt Year Right 40
    colMarketValue Market Value Right 75
    colSaleStatus Status   80
    Picture Box Picture Box pbxProperty   Top, Bottom, Left, Right
    Tab Page     Real Estate Lising  
    Data Grid View Data Grid View dgvProperties   Top, Bottom, Left, Right
    List View List View lvwPropertiesStatistics Properties Statistics Bottom, Left, Right
    Button Button btnNewProperty New Real Estate Property ... Bottom, Right
    Button Button btnPropertyEditor Edit Real Estate Property ... Bottom, Right
    Button Button btnClose Close Bottom, Right
  34. Right-click anywhere on the form and click View Code
  35. Change the document as follows:
    internal void ShowProperties()
    {
        using (SqlConnection scAltairRealtors =
            new SqlConnection("Data Source=(local);" +
                               "Database='AltairRealtors1';" +
                               "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='AltairRealtors1';" +
                               "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;", 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();
        }
    }
    
    private void AltairRealtors_Load(object sender, EventArgs e)
    {
        ShowProperties();
    }
  36. Return to the Altair Realtors form and click the Properties Summary list view
  37. In the Properties window, click Events and double-click ItemSelectionChanged
  38. Implement the event as follows:
    private void lvwProperties_ItemSelectionChanged(object sender, ListViewItemSelectionChangedEventArgs e)
    {
        // Get a list of the picture files from the Altair Realtors folder
        string strDirectory = "C:\\Altair Realtors";
        DirectoryInfo dirProperties = new DirectoryInfo(strDirectory);
        FileInfo[] PictureFiles = dirProperties.GetFiles("*.jpg");
    
        bool pictureExists = false;
    
        // Look for a file that holds the same name as the property number
        foreach (FileInfo fle in PictureFiles)
        {
            // Get the name of the file without its extension
            string fwe = Path.GetFileNameWithoutExtension(fle.FullName);
    
            if (fwe == e.Item.SubItems[0].Text)
            {
                pbxProperty.Image = Image.FromFile(strDirectory + "\\" + e.Item.SubItems[0].Text + ".jpg");
                pictureExists = true;
            }
    
            if (pictureExists == false)
                pbxProperty.Image = Image.FromFile("C:\\Altair Realtors\\default.jpg");
        }
    }
  39. Return to the Altair Realtors form and double-click the New Real Estate Property button
  40. Implement the event as follows:
    private void btnNewProperty_Click(object sender, EventArgs e)
    {
        int iFinishedBasement = 0;
        int iIndoorGarage = 0;
        NewRealEstateProperty rep = new NewRealEstateProperty();
    
        if (rep.ShowDialog() == System.Windows.Forms.DialogResult.OK)
        {
            if (rep.chkFinishedBasement.Checked == true)
                iFinishedBasement = 1;
            if (rep.chkIndoorGarage.Checked == true)
                iIndoorGarage = 1;
    
            using (SqlConnection scAltairRealtors =
                new SqlConnection("Data Source=(local);" +
                                   "Database='AltairRealtors1';" +
                                   "Integrated Security='SSPI';"))
            {
                SqlCommand cmdProperty =
                    new SqlCommand("INSERT INTO Listings.Properties(PropertyNumber, " +
                                   "PropertyType, [Address], City, [State], ZIPCode, " +
                                   "Bedrooms, Bathrooms, Stories, FinishedBasement, " +
                                   "IndoorGarage, YearBuilt, Condition, MarketValue, " +
                                   "SaleStatus) VALUES(N'" + rep.txtPropertyNumber.Text + "', N'" +
                                   rep.cbxPropertyTypes.Text + "', N'" + rep.txtAddress.Text + "', N'" +
                                   rep.txtCity.Text + "', N'" + rep.cbxStates.Text + "', N'" +
                                   rep.txtZIPCode.Text + "', " + int.Parse(rep.txtBedrooms.Text) + ", " +
                                   float.Parse(rep.txtBathrooms.Text) + ", " +
                                   int.Parse(rep.txtStories.Text) + ", " + iFinishedBasement + ", " +
                                   iIndoorGarage + ", " + int.Parse(rep.txtYearBuilt.Text) + ", N'" +
                                   rep.cbxConditions.Text + "', " + decimal.Parse(rep.txtMarketValue.Text)
                                   + ", N'" + rep.cbxSaleStatus.Text + "');",
                                   scAltairRealtors);
    
                scAltairRealtors.Open();
                cmdProperty.ExecuteNonQuery();
    
                if (!(string.IsNullOrEmpty(rep.strPictureFile)))
                {
                    FileInfo flePicture = new FileInfo(rep.strPictureFile);
                    flePicture.CopyTo("C:\\Altair Realtors\\" + rep.txtPropertyNumber.Text + flePicture.Extension);
                }
            }
        }
    
        ShowProperties();
    }
  41. Return to the Altair Realtors form and double-click the Edit Real Estate Property button
  42. Implement the event as follows:
    private void btnPropertyEditor_Click(object sender, EventArgs e)
    {
        PropertyEditor editor = new PropertyEditor();
    
        using (SqlConnection scAltairRealtors =
                    new SqlConnection("Data Source=(local);" +
                                       "Database='AltairRealtors1';" +
                                       "Integrated Security='SSPI';"))
        {
            SqlCommand cmdProperty =
                new SqlCommand("UPDATE Listings.Properties SET PropertyType = N'" + editor.cbxPropertiesTypes.Text + "' WHERE PropertyNumber = N'" + editor.txtPropertyNumber.Text + "';" +
                               "UPDATE Listings.Properties SET [Address] = N'" + editor.txtAddress.Text + "' WHERE PropertyNumber = N'" + editor.txtPropertyNumber.Text + "';" +
                               "UPDATE Listings.Properties SET City = N'" + editor.txtCity.Text + "' WHERE PropertyNumber = N'" + editor.txtPropertyNumber.Text + "';" +
                               "UPDATE Listings.Properties SET [State] = N'" + editor.cbxStates.Text + "' WHERE PropertyNumber = N'" + editor.txtPropertyNumber.Text + "';" +
                               "UPDATE Listings.Properties SET ZIPCode = N'" + editor.txtZIPCode.Text + "' WHERE PropertyNumber = N'" + editor.txtPropertyNumber.Text + "';" +
                               "UPDATE Listings.Properties SET Bedrooms = " + int.Parse(editor.txtBedrooms.Text) + " WHERE PropertyNumber = N'" + editor.txtPropertyNumber.Text + "';" +
                               "UPDATE Listings.Properties SET Bathrooms = " + float.Parse(editor.txtBathrooms.Text) + " WHERE PropertyNumber = N'" + editor.txtPropertyNumber.Text + "';" +
                               "UPDATE Listings.Properties SET Stories = " + int.Parse(editor.txtStories.Text) + " WHERE PropertyNumber = N'" + editor.txtPropertyNumber.Text + "';" +
                               "UPDATE Listings.Properties SET FinishedBasement " + editor.chkFinishedBasement.Checked + " WHERE PropertyNumber = N'" + editor.txtPropertyNumber.Text + "';" +
                               "UPDATE Listings.Properties SET IndoorGarage = " + editor.chkIndoorGarage.Checked + " WHERE PropertyNumber = N'" + editor.txtPropertyNumber.Text + "';" +
                               "UPDATE Listings.Properties SET YearBuilt = " + int.Parse(editor.txtYearBuilt.Text) + " WHERE PropertyNumber = N'" + editor.txtPropertyNumber.Text + "';" +
                               "UPDATE Listings.Properties SET Condition = N'" + editor.cbxConditions.Text + "' WHERE PropertyNumber = N'" + editor.txtPropertyNumber.Text + "';" +
                               "UPDATE Listings.Properties SET MarketValue = " + decimal.Parse(editor.txtMarketValue.Text) + "' WHERE PropertyNumber = N'" + editor.txtPropertyNumber.Text + "';" +
                               "UPDATE Listings.Properties SET SaleStatus = N'" + editor.cbxSalesStatus.Text + "' WHERE PropertyNumber = N'" + editor.txtPropertyNumber.Text + "';",
                               scAltairRealtors);
    
            scAltairRealtors.Open();
            cmdProperty.ExecuteNonQuery();
        }
    
        editor.ShowDialog();
    }
  43. Return to the form and click the list view
  44. Execute the application and create the properties
    Altair Realtors - Properties Listing
    Altair Realtors - Properties Listing
  45. Close the form and return to your programming environment

Creating an Aggregate Query

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:

SELECT WhatField(s)
FROM WhatObject(s)
GROUP BY Column(s)

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.

Practical LearningPractical Learning: Creating an Aggregate Query

  1. Display the Altair Realtors form.
    On the form, right-click the bottom list view and click Edit columns
  2. Add a column with the following characteristics:
    (Name): colPropertyCategory
    Text: Property Category
    Width: 120
 
 
 

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 LearningPractical Learning: Getting the Number of Records

  1. 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
  2. Click OK
  3. Right-click the form and click View Code
  4. 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);
            }
        }
    }
  5. Execute the application

    Altair Realtors - Properties Listing

  6. 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 LearningPractical Learning: Getting the Minimum of a Series

  1. Right-click the bottom list view and click edit columns
  2. In the ColumnHeader Collection Editor, click Add and create a column with the following characteristics:
    (Name): colMinimum
    Text: Cheapest Property
    TextAlign: Right
    Width: 100
  3. Click OK
  4. Right-click the form and click View Code
  5. 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);
            }
        }
    }
  6. Execute the application

    Altair Realtors - Properties Listing

  7. 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 LearningPractical Learning: Getting the Highest Value of a Series

  1. Right-click the bottom list view and click edit columns
  2. In the ColumnHeader Collection Editor, click Add and create a column with the following characteristics:
    (Name): colMaximum
    Text: Most Expensive
    TextAlign: Right
    Width: 88
  3. Click OK
  4. Right-click the form and click View Code
  5. 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);
            }
        }
    }
  6. Execute the application

    Altair Realtors - Properties Listing

  7. 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 LearningPractical Learning: Getting the Sum of Value

  1. Right-click the bottom list view and click edit columns
  2. 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
  3. Click OK
  4. Right-click the form and click View Code
  5. 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);
            }
        }
    }
  6. Execute the application

    Altair Realtors - Properties Listing

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

Mean

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 LearningPractical Learning: Getting the Mean of Value

  1. Right-click the bottom list view and click edit columns
  2. 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
  3. Click OK
  4. Right-click the form and click View Code
  5. 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);
            }
        }
    }
  6. Execute the application

    Altair Realtors - Properties Listing

  7. 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 LearningPractical Learning: Getting the Standard Deviation

  1. Right-click the bottom list view and click edit columns
  2. In the ColumnHeader Collection Editor, click Add and create a column with the following characteristics:
    (Name): colStandardDeviation
    Text: Standard Deviation
    TextAlign: Right
    Width: 105
  3. Click OK
  4. Right-click the form and click View Code
  5. 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);
            }
        }
    }
  6. Execute the application

    Altair Realtors - Properties Listing

  7. 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 LearningPractical Learning: Getting the Sample Variance

  1. Right-click the bottom list view and click edit columns
  2. In the ColumnHeader Collection Editor, click Add and create a column with the following characteristics:
    (Name): colVariance
    Text: Market Variance
    TextAlign: Right
    Width: 90
  3. Click OK
    Sample Variance
  4. Right-click the form and click View Code
  5. 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();
    }
  6. Execute the application

    Altair Realtors - Properties Listing

  7. 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 LearningPractical Learning: Setting a Condition

  1. Add a label to the bottom section of the form and change its characteristics as follows:
    Anchor: Bottom, Left
    Text: Show Statistics for Only:
  2. 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
    Sample Variance
  3. 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);
            }
        }
    }
  4. Execute the application
  5. Select an option, such as Single Family, from the combo box

    Altair Realtors - Properties Listing

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

 
 
   
 

Home Copyright © 2014, FunctionX