How To Use Group By Statement In Mssql

Hello Friend Today In This Blog I Will Show You How To Use Group By Statement In Mssql.The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.HAVING filters records that work on summarized GROUP BY results.HAVING applies to summarized group records, whereas WHERE applies to individual records.Only the groups that meet the HAVING criteria will be returned.HAVING requires that a GROUP BY clause is present.WHERE and HAVING can be in the same query.The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.

The basic syntax of a GROUP BY clause is shown in the following code block. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.The SQL GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.This SQL tutorial explains how to use the SQL GROUP BY clause with syntax and examples.The SQL GROUP BY Clause is used along with the group functions to retrieve data grouped according to one or more columns.The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns.SQL aggregate functions like COUNT, AVG, and SUM have something in common: they all aggregate across the entire table. But what if you want to aggregate only part of a table? For example, you might want to count the number of entries for each year.In situations like this, you’d need to use the GROUP BY clause. GROUP BY allows you to separate data into groups, which can be aggregated independently of one another. The GROUP BY clause is a SQL command that is used to group rows that have the same values.The GROUP BY clause is used in the SELECT statement .Optionally it is used in conjunction with aggregate functions to produce summary reports from the database.That's what it does, summarizing data from the database.The queries that contain the GROUP BY clause are called grouped queries and only return a single row for every grouped item.The usage of SQL GROUP BY clause is, to divide the rows in a table into smaller groups.The GROUP BY clause is used with the SQL SELECT statement.The grouping can happen after retrieves the rows from a table.
When some rows are retrieved from a grouped result against some condition, that is possible with HAVING clause.The GROUP BY clause is used with the SELECT statement to make a group of rows based on the values of a specific column or expression. The SQL AGGREGATE function can be used to get summary information for every group and these are applied to an individual group.The WHERE clause is used to retrieve rows based on a certain condition, but it can not be applied to grouped result.In an SQL statement, suppose you are using GROUP BY, if required you can use HAVING instead of WHERE, after GROUP BY.

GROUP BY Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)

ORDER BY column_name(s);

Here Is An Example Related To Topic

Step 1 :Create database

Create database BMC

Step 2:Create table

Here In This step We Will Create table.

Here In This Step We Required Four Column With Name Cids ,Cname ,Ccity And CPrice using Parameter bigint,varchar and float.

create table Client_table_news

(

Cids bigint,

Cname varchar(50),

Ccity varchar(200),

CPrice float

);

Step 3 :Create Insert Trigger Functionality

Here In This Step We Will Insert Data Using Insert Command.

Data Will Be Added Manually Using Insert Command.

Insert Into Client_table_news values(1,'VIRAJ','MUMBAI',15000.00)

Insert Into Client_table_news values(2,'RAJ','DELHI',10000.00)

Insert Into Client_table_news values(3,'ANIL','PUNE',12000.00)

Insert Into Client_table_news values(4,'SUNIL','MUMBAI',9000.00)

Insert Into Client_table_news values(5,'SHERYAS','JAIPUR',11000.00)

Insert Into Client_table_news values(6,'AJAY','MUMBAI',16000.00)

select *from Client_table_news

Step 4 :Create Group By Statement

Here In This Step We Will Create Group By Statement By Passing Parameter

GROUP BY Ccity;.By Passing The Parameter We Get The List Of City Available
In Database.

SELECT COUNT(Cids)As NumberOfCity, Ccity
FROM Client_table_news

GROUP BY Ccity;

SELECT COUNT(Cids)As NumberOfCity, Ccity
FROM Client_table_news
GROUP BY Ccity

ORDER BY COUNT(Cids) DESC;