Skip to main content
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

FunctionDescription
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
DatabaseOutput
PostgreSQLSELECT status, COUNT(*) FROM users GROUP BY status
MongoDBdb.users.aggregate([{ $group: { _id: '$status', count: { $sum: 1 } } }])

​
Sum by Group

:SUM amount FROM Order GROUP BY status
DatabaseOutput
PostgreSQLSELECT status, SUM(amount) FROM orders GROUP BY status
MongoDBdb.orders.aggregate([{ $group: { _id: '$status', total: { $sum: '$amount' } } }])

​
Average by Group

:AVG price FROM Product GROUP BY category
DatabaseOutput
PostgreSQLSELECT 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
DatabaseOutput
PostgreSQLSELECT status, SUM(amount) AS total FROM orders GROUP BY status

​
Group by Multiple Columns

:SUM amount FROM Order GROUP BY status, user_id
DatabaseOutput
PostgreSQLSELECT 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
DatabaseOutput
PostgreSQLSELECT status, COUNT(*) FROM users GROUP BY status HAVING COUNT(*) > 10

​
WHERE vs HAVING

  • WHERE filters rows before grouping
  • HAVING 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