Aggregate results
This page is part of the magic.data.common documentation.
You can also create aggregate results, by simply adding your aggregate as your column, such as the following illustrates.
sql.read
table:table1
limit:-1
columns
count(*)
as:count
The above will result in the following SQL.
select count(*) as 'count' from 'table1'
A column, [group] or [order] entry containing a parenthesis is parsed as an expression, and every
identifier in it is escaped. An expression can call the functions every database has, being count, sum,
avg, min, max, group_concat, string_agg, round, abs, coalesce, lower, upper, length and
cast, plus the functions of the database’s own dialect.
- SQLite -
total,strftime,date,datetime,julianday,ifnull,random,substrandtrim - MySQL -
date_format,year,month,day,ifnull,rand,concat,substringandtrim - PostgreSQL -
to_char,date_trunc,random,concat,substringandtrim - SQL Server -
year,month,day,format,isnull,newid,concat,substringandtrim
Functions can be nested, and combined with +, -, * and /, numbers, and string literals in single quotes
holding letters, digits, spaces and the characters %-_:/.,. A column can declare its alias inline, as in
count(*) as total, instead of with an [as] argument. The following groups SQLite rows by month.
sql.read
table:sales
limit:-1
columns
strftime('%Y-%m', sold)
as:month
round(sum(amount), 2) as total
group
strftime('%Y-%m', sold)
order:"strftime('%Y-%m', sold)"
Anything else, such as sub-selects, case expressions, functions not listed above, or a string literal holding
a quote, is rejected with an exception, to avoid SQL injection attacks. Use [data.select] with plain SQL for
those.
By setting [limit] to “-1”, like we do above, we avoid adding the limit parts to our SQL. Unless you explicitly specify a limit, the default value will always be 25 to avoid accidentally exhausting your database, and/or web server, by selecting all records from a table with millions of records.
| ← How to use [data.*] | all of magic.data.common | Paging → |