Documentation Index
Fetch the complete documentation index at: https://docs.omniql.com/llms.txt
Use this file to discover all available pages before exploring further.
Aggregate data using GROUP BY with aggregate functions.
Basic Syntax
:COUNT * FROM Entity GROUP BY field
:SUM field FROM Entity GROUP BY field
:AVG field FROM Entity GROUP BY field
Aggregate Functions
| Function | Description |
|---|
COUNT(*) | Count rows |
SUM(column) | Sum values |
AVG(column) | Average value |
MIN(column) | Minimum value |
MAX(column) | Maximum value |
Count by Group
:COUNT * FROM User GROUP BY status
| Database | Output |
|---|
| PostgreSQL | SELECT status, COUNT(*) FROM users GROUP BY status |
| MongoDB | db.users.aggregate([{ $group: { _id: '$status', count: { $sum: 1 } } }]) |
Sum by Group
:SUM amount FROM Order GROUP BY status
| Database | Output |
|---|
| PostgreSQL | SELECT status, SUM(amount) FROM orders GROUP BY status |
| MongoDB | db.orders.aggregate([{ $group: { _id: '$status', total: { $sum: '$amount' } } }]) |
Average by Group
:AVG price FROM Product GROUP BY category
| Database | Output |
|---|
| PostgreSQL | SELECT category, AVG(price) FROM products GROUP BY category |
Min/Max by Group
:MIN price FROM Product GROUP BY category
:MAX price FROM Product GROUP BY category
Multiple Columns with GET WITH
For multiple columns with aggregates (except COUNT):
:GET Order WITH status, SUM(amount) AS total GROUP BY status
| Database | Output |
|---|
| PostgreSQL | SELECT status, SUM(amount) AS total FROM orders GROUP BY status |
Group by Multiple Columns
:SUM amount FROM Order GROUP BY status, user_id
| Database | Output |
|---|
| PostgreSQL | SELECT status, user_id, SUM(amount) FROM orders GROUP BY status, user_id |
HAVING Clause
Filter groups after aggregation.
:COUNT * FROM User GROUP BY status HAVING COUNT(*) > 10
| Database | Output |
|---|
| PostgreSQL | SELECT status, COUNT(*) FROM users GROUP BY status HAVING COUNT(*) > 10 |
WHERE vs HAVING
WHERE filters rows before groupingHAVING filters groups after aggregation
:SUM amount FROM Order WHERE created_at > "2024-01-01" GROUP BY status HAVING SUM(amount) > 1000
With ORDER BY
:COUNT * FROM Product GROUP BY category ORDER BY count DESC
Complete Example
:SUM amount FROM Order
WHERE created_at BETWEEN "2024-01-01" AND "2024-12-31"
GROUP BY status
HAVING SUM(amount) >= 500
ORDER BY sum DESC
LIMIT 100
Next Steps
Window Functions
Advanced analytics
Insert Data
Create new records
