SWQL supports the count, min, max, sum, and average aggregate functions. These functions are used to perform operations on batches of records. The results of these functions must be aliased to a new name.
SELECT COUNT([Nodes].Caption) AS [Node Quantity]
FROM Orion.Nodes AS [Nodes]
When returning any additional fields, these must be identified with a GROUP BY clause.
SELECT COUNT([Nodes].Caption) AS [Node Quantity]
, [Nodes].Vendor
FROM Orion.Nodes AS [Nodes]
GROUP BY [Nodes].Vendor
This query will return the count of nodes broken down (grouped by) the vendor.
Filtering on Aggregate Values
To filter on the results returned by an aggregate function, you must include the HAVING clause. The WHERE clause operates on the individual records and the HAVING operates on the aggregation results. The syntax is nearly identical, but the placement of the HAVING clause is after the GROUP BY clause.
SELECT AVG([Nodes].ResponseTime) AS [Average RT]
, [Nodes].Vendor
FROM Orion.Nodes AS [Nodes]
GROUP BY [Nodes].Vendor
HAVING AVG([Nodes].ResponseTime) > 3
The above query will return the average response time broken down by the vendor, but only return those where the value of the resulting average is greater than 3. Like WHERE clauses, complex Boolean operations are supported (using BETWEEN, IN, etc.). Alternatively, this same query could be done with a subquery, but is more difficult to comprehend for beginners.
SELECT [ResponseByVendor].[Average RT]
, [ResponseByVendor].Vendor
FROM (
SELECT AVG([Nodes].ResponseTime) AS [Average RT]
, [Nodes].Vendor
FROM Orion.Nodes AS [Nodes]
GROUP BY [Nodes].Vendor
) AS [ResponseByVendor]
WHERE [ResponseByVendor].[Average RT] > 3