Aggregates
Introduction
Starcounter SQL supports five different aggregates:
AVG, SUM, COUNT, MAX, and MIN. These can be used with grouping and conditions on groups with the GROUP BY and HAVING clauses.Example
1
SELECT AVG(e.Salary), MAX(e.Salary), MIN(e.Salary), e.Department
2
FROM Example.Employee e
3
GROUP BY e.Department
4
HAVING SUM(e.Salary) > 20000
Copied!
Using Asterisk Shorthand With COUNT
COUNTThe asterisk shorthand is treated as a literal in
COUNT. Since Db.SQL doesn't support literals, using COUNT(*) in Db.SQL will throw ScErrUnsupportLiteral (SCERR7029). There are three ways to work around this:
1
// This throws SCERR7029
2
Db.SQL("SELECT COUNT(*) FROM Person").First();
3
// Using an identifier instead of * works
4
Db.SQL("SELECT COUNT(p) FROM Person p").First();
5
// Db.SlowSQL supports literals, so it can be used
6
Db.SlowSQL("SELECT COUNT(*) FROM Person").First();
7
// Linq can also give you the number of rows
8
Db.SQL("SELECT p FROM Person p").Count();
Copied!
The first option of using an identifier to get the count best in most cases, both for versatility and performance.
Last modified 4yr ago
Copy link