SQL Group BY Command in ASP.NET
See more tutorials in Database. This post has Comments Off on SQL Group BY Command in ASP.NET.
This Tutorial will teach you how to use the SQL Group By command and when it is needed. The Group By command is an advanced SQL Query Command used to sort a table by groups of one item in a column. It can be very useful in large SQL Tables.
Step1. Creating the Table
1. Create an SQL Table in your project called “Sales”
2. Make 3 columns “ID”, “Branch”, and “Sales”
3. Fill the Table with Data like below:
|ID(int, identity, primary key)||Branch(varchar(50))||Sales(int)|
4. We are going to Group By Branch and have it sum up the total sales and display them, the code below will do just that.
Step 2. Creating The SQL Command
1. The Code below will use the Group By Command to grab the different Branches out of the Table and display their Total Sales to a GridView Control.
2. The Group By command only grabs Distinct values in a column, so this example would return New York, and Florida with their respective totals. Copy the Code below and run the project to see the results.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<form id="form1" runat="server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT Branch, SUM(SalesAmount) as Total FROM [Sales]
GROUP BY Branch"></asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1">
In This Tutorial we learned how to use the SQL Group By Command to group a list by Branch and add up the sales of the recurring Branches. This command can come in handy when using large databases to help you generalize the items you want to get. Just remember that you are unable to pull out a column unless it is an aggregate function or it is in the Group By statement. If you are trying to do something like that it may be better to use the Join command instead.