Advanced SELECT statement combined with data aggregation functions
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..|
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;
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
SQL Server provides several methods for aggregating two individual geometric or geographic data items.
|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.|
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 [email protected](@city2) SELECT @CombinedCity
Some spatial aggregation functions
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.
SELECT Geography::UnionAggregate (SpatialLocation) AS AVGLocation FROM Person.Address WHERE City='London';
SELECT Geography::EnvelopeAggregate(SpatialLocation) AS Location FROM Person.Address WHERE City='London'
Convex Hull Aggregate
SELECT Geography::ConvexHullAggregate(SpatialLocation) AS Location FROM Person.Address WHERE City='Lodon'