Executes aggregate functions. More...
Import Statement: | import ArcGIS.AppFramework.Sql 1.0 |
Inherits: |
Properties
- finalize : var
- initialize : var
- iterate : var
Detailed Description
Aggregate functions return a single result row based on a group of rows, rather than single rows. Typical aggregate functions include MIN(), MAX() and SUM().
When the SQLite driver is used through the driverName property in Sql being set to 'QSQLITE', then you can use SqlAggregateFunction to define custom aggregate functions in JavaScript.
The following code sample defines a new aggregate function called 'average', to provide the average of values within the database. Be aware that, to support the TableView and TableViewColumn components, you will need to import QtQuick.Controls 1.4, rather than the latest version (you can import both).
Item { property var averageArea ColumnLayout { anchors.fill: parent anchors.margins: 10 * AppFramework.displayScaleFactor spacing: 10 * AppFramework.displayScaleFactor TableView { id: tableView Layout.fillWidth: true Layout.fillHeight: true TableViewColumn { role: "area" title: qsTr("Area") width: 200 * AppFramework.displayScaleFactor } } Text { text: qsTr("Average Area: %1").arg(averageArea) } } SqlDatabase { id: db databaseName: ":memory:" SqlAggregateFunction { name: "average" initialize: average_initialize iterate: average_iterate finalize: average_finalize } } Component.onCompleted: { db.open(); db.query("CREATE TABLE Parcel ( AREA real ); "); db.query("INSERT INTO Parcel VALUES (:area) ", { area: 110.0 } ); db.query("INSERT INTO Parcel VALUES (:area) ", { area: 130.0 } ); db.query("INSERT INTO Parcel VALUES (:area) ", { area: 170.0 } ); db.query("INSERT INTO Parcel VALUES (:area) ", { area: 190.0 } ); tableView.model = db.tableModel("Parcel"); var query = db.query("SELECT average(AREA) as avg FROM Parcel "); var ok = query.first(); if (ok) { averageArea = query.values.avg; // 150.0 } query.finish(); } function average_initialize() { var context = { sum: 0, count: 0 } return context; } function average_iterate(context, value) { context.sum += value; context.count++; } function average_finalize(context) { return context.count ? context.sum / context.count : Number.NaN; } }
Property Documentation
Refers to your JavaScript function which finalizes your context and returns a single row.
Refers to your JavaScript function for creating the initial context of the aggregate function.
Refers to your JavaScript function which is used to incrementally update your context with values from the database.