-

WHERE VS GROUP BY VS HAVING:


Group by clause
The Group by clause is often used to arrange identical duplicate data into groups with a select statement to group the result-set by one or more columns. This clause works with the select specific list of items, and we can use HAVING, and ORDER BY clauses. Group by clause always works with an aggregate function like MAX, MIN, SUM, AVG, COUNT.
Let us discuss group by clause with an example. We have a VehicleProduction table and there are some models with a price and it has some duplicate data. We want to categorize this data in a different group with a respective total price.
Example
  1. Create table VehicleProduction  
  2. (  
  3. Id int primary key Identity,   
  4. Model varchar(50),  
  5. Price money  
  6. )  
  7.   
  8. Insert into VehicleProduction values('L551', 850000),('L551', 850000),('L551', 850000),('L551', 750000),  
  9. ('L538', 650000),('L538', 650000),('L538', 550000),('L530', 450000),('L530',350000), ('L545', 250000)  
  10.   
  11. Select * from VehicleProduction   
Output 
 
Aggregate Functions
MAX() - function returns the maximum value of the numeric column of specified criteria.
Example
  1. Select max(Price) As 'MaximumCostOfModel' from VehicleProduction   
Output
 
MIN() – function returns the minimum of the numeric column of specified criteria
Example
  1. Select Min(Price) As 'MinimumCostOfModel' from VehicleProduction   
Output
 
SUM() - function returns the total sum of a numeric column of specified criteria.
Example
  1. Select SUM(Price) As 'SumCostOfAllModel' from VehicleProduction   
Output
 
AVG() - function returns the average value of a numeric column of specified criteria.
Example
  1. Select AVG(Price) As 'AverageCostOfModel' from VehicleProduction   
Output
 
COUNT() - function returns the number of rows that match specified criteria.
Example
  1. Select Count(Price) As 'TotalVehicleModels' from VehicleProduction   
Output
 
Distinct clause
The distinct clause is used to filter unique records out of the duplicate records that satisfy the query criteria.
Example
  1. Select Distinct(Model),  Price from VehicleProduction   
Output
Group by clause
The Group by clause is often used to arrange the identical duplicate data into groups with the select statement. This clause works with the select specific list of items, for that we can use HAVING, and ORDER BY clauses
Syntax
  1. SELECT Column1, Column2  
  2. FROM TableName  
  3. GROUP BY Column1, Column2   
Example
  1. Select * from VehicleProduction   
  2.   
  3. Select Model, Price from VehicleProduction   
  4. group by Model, Price   
Output 
 
Let’s look at an example of GROUP BY with aggregate functions.
GROUP BY with aggregate functions
Example
  1. Select Model, Price, Count(*) As QtyOfModel, Sum(Price) As TotPriceOfModel  from VehicleProduction   
  2. group by Model, Price    
Output
 
Where clause
Where clause works with select clause but won’t work on the group by or aggregate function condition.
Example 1
  1. Select Model, Price from VehicleProduction   
  2. where Model != 'L530'  
  3. group by Model, Price    
Output
 
Example 2:  We can’t use where clause after group by clause
  1. Select Model, Price from VehicleProduction   
  2. group by Model, Price   
  3. where Model != 'L530'   
Output
 
Having clause
Having clause works with a group by clause but specifically works on aggregate function condition.
Example
  1. Select Model, Price from VehicleProduction   
  2. Group by Model, Price   
  3. Having SUM(Price)  > 600000.00  
 Output
 
ORDER BY clause
Order By clause shows the records in ascending or descending order of the specific condition
Example
  1. Select Model, Price from VehicleProduction   
  2. Group by Model, Price   
  3. Having SUM(Price)  > 400000.00   
  4. order by Price desc   
Output
 

Comments

Post a Comment

Popular posts from this blog

-
Image
CLUSTERED INDEX VS NON CLUSTERED INDEX Clustered Index Only one clustered index can be there in a table because of the sorted order Sort the records and store them physically according to the order Data retrieval is faster than non-clustered indexes Do not need extra space to store logical structure A primary key by default creates a clustered index on it. Non Clustered Index There can be any number of non-clustered indexes in a table because of the number of unique keys we can create. Do not affect the physical order. Create a logical order for data rows and use pointers to physical data files Data insertion/update is faster than clustered index Use extra space to store logical structure A unique key/keys by dafault created an unclustered index on it. In Depth Explanation: Clustered Technically speaking, a clustered index is a B-Tree data structure where all the rows in a table are stored at the leaf level of the index. In other words, a clustered ind...
Read more
-
Image
SQL JOINS Tutorial: INNER, OUTER, LEFT, RIGHT, CROSS What are JOINS? Joins help retrieving data from two or more database tables. The tables are mutually related using primary and foreign keys. id first_name last_name movie_id 1 Adam Smith 1 2 Ravi Kumar 2 3 Susan Davidson 5 4 Jenny Adrianna 8 6 Lee Pong 10 id title category 1 ASSASSIN'S CREED: EMBERS Animations 2 Real Steel(2012) Animations 3 Alvin and the Chipmunks Animations 4 The Adventures of Tin Tin Animations 5 Safe (2012) Action 6 Safe House(2012) Action 7 GIA 18+ 8 Deadline 2009 18+ 9 The Dirty Picture 18+ 10 Marley and me Romance   Types of joins Cross JOIN Cross JOIN is a simplest form of JOINs which matches each row from one database table to all rows of another. In other words it gives us combinations of each row of first table with all records in second table. Suppose we want to get all member records against all the movie records, we can use the script shown below to ge...
Read more
-
Image
COMMON TABLE EXPRESSIONS(cte): It is used best used to extract the hierarchical data  and it is best explained with an example: Problem Many organizations have some type of hierarchy for business processes. When it comes to large organizations, the hierarchy can get very complex and large, so building a hierarchy in a RDBMS is a tedious task. We have to create views, cursors and so on, but using a CTE in SQL Server is a better solution to retrieve hierarchy-based data. Solution Common Table Expressions (CTE) have two types, recursive and non-recursive. We will see how the recursive CTE works with examples. A recursive CTE can be explained in three parts: Anchor Query: This is the first statement which is executed. This query will give the base data for the CTE. Separator: This is the middle part where in we generally use a UNION ALL and few more operators. Recursive Query: This is the main part, this is the CTE query which refers to the same CTE by recursion. ...
Read more