Advanced SELECT statement combined with data aggregation functions

24-07-2022
Toanngo92
0 Comments

Mục lục

Data Aggregation Functions

At times, developers may also ask to perform analysis on rows, such as counting rows, meeting specific criteria, or summarizing sales for all orders. The aggregate function allows to do it.

Since aggregate functions return a single value, they can be used in SELECT states where an expression is used, such as SELECT, HAVING, and ORDER BY clauses. Aggregate functions ignore NULLS, except when using COUNT(*).

Aggregate functions in SELECT list will not have column names, may want to use AS clause to set column names.

The aggregate function is a SELECT clause that operates on all rows passed to the SELECT phase. Without the GROUP BY clause, all rows will be summarized.

Some aggregate functions:

Function name	Syntax _	Describe
AVG	AVG(<expression>)	Calculate the average of all non-NULL values in column
COUNT or COUNT_BIG	COUNT(*) or COUNT(<expression>)	When (*) is used, this function counts all rows, including those with NULLs. The function counts all rows, including those with NULLs. The function returns the number of non-NULL rows for the column when a column is specified as . The return value of the COUNT function is an int. The return value of COUNT_BIG is big_int
MAX	MAX(<expression>)	Returns the largest number, the most recent date/time, or the most recent occurrence string.
MIN	MIN(<expression>)	Returns the smallest number, the furthest date or time, or the string that occurs first
SUMMARY	SUM(<expression>)	Sum all non-NULL values in column..

For example:

 SELECT AVG([UnitPrice]) AS AvgUnitPrice, MIN([OrderQty]) AS MinQty, MAX([UnitPriceDiscount]) AS MaxDiscount FROM Sales.SalesOrderDetail;

When using aggregation in the SELECT clause, all distinct columns in the SELECT list must be used as input to an aggregate function or must be referenced in the GROUP BY clause. If it fails, an error will occur.

Example query that will return an error:

 SELECT SalesOrderID,AVG(UnitPrice) AS AvgPrice FROM Sales.SalesOrderDetail;

Error returned:

 Column 'Sales.SalesOrderDetail.SalesOrderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

To fix this, simply remove the SalesOrderID column in the SELECT statement.

Besides using aggregate functions with numeric data, can be used with date, time, character data.

Example using MIN and CODE to get the nearest and farthest orderdate

 SELECT MIN(OrderDate) AS Earliest, MAX(OrderDate) AS Lastest FROM Sales.SalesOrderHeader

Space synthesis

SQL Server provides several methods for aggregating two individual geometric or geographic data items.

Method	Describe
STUnion	Returns an object that represents the union of one geometry/geo feature with another geometry/geographic feature.
STIntersection	Returns an object representing the intersection of one geometry/geo feature and another geometry/geographic feature
STConvexHull	Returns an aobject representing the convex part of a geometric/geographic feature. A set of points is said to be convex if for any two points the entire segment is contained in that set. The convex hull of a set of points is the smallest convex set containing the set. For any given set of points, there is only one convex shell.

For example:

 SELECT geometry::Point(251,1,4326).STUnion(geometry::Point(252,2,4236));

Example 2:

 DECLARE @City1 geography SET @City1=geography::STPolyFromText('POLYGON((175.3 -41.5,183.3 -37.9,172.8 -34.6,175.3 -41.5))',4326) DECLARE @City2 geography SET @City2=geography::STPolyFromText('POLYGON((169.3 -46.6,174.3 -41.6,172.5 -40.7,166.3 -45.8,169.3 -46.6))',4326) DECLARE @CombinedCity geography=@City1.STUnion(@city2) SELECT @CombinedCity

Some spatial aggregation functions

Union Aggregate

It performs the merge operation on a set of geometric objects. It combines multiple spatial objects into a single spatial object, removing internal boundaries, if any.

For example:

 SELECT Geography::UnionAggregate (SpatialLocation) AS AVGLocation FROM Person.Address WHERE City='London';

Envelope Aggregate

For example:

 SELECT Geography::EnvelopeAggregate(SpatialLocation) AS Location FROM Person.Address WHERE City='London'

Convex Hull Aggregate

For example:

 SELECT Geography::ConvexHullAggregate(SpatialLocation) AS Location FROM Person.Address WHERE City='Lodon'