Aggregation
CORMO supports some basic aggregation operations via Query#group.
| Description | CORMO | SQL | MongoDB |
|---|---|---|---|
| Count all | Order.group(null, { count: { $sum: 1 } }) | SELECT COUNT(*) AS count FROM orders | db.orders.aggregate([ {$group:{_id:null,count:{$sum:1}}} ]) |
| Sum all | Order.group(null, { total: { $sum: '$price' } }) | SELECT SUM(price) AS total FROM orders | db.orders.aggregate([ {$group:{_id:null,total:{$sum:'$price'}}} ]) |
| Only for filtered records | Order.where({ price: { $lt: 10 } }) .group(null, { count: { $sum: 1 }, total: { $sum: '$price' } }) | SELECT COUNT(*) AS count, SUM(price) AS total FROM orders WHERE price<10 | db.orders.aggregate([ {$match:{price:{$lt:10}}}, {$group:{_id:null,count:{$sum:1},total:{$sum:'$price'}}} ]) |
| Grouping | Order.group('customer', { count: { $sum: 1 }, total: { $sum: '$price' } }) | SELECT customer, COUNT(*) AS count, SUM(price) AS total FROM orders GROUP BY customer | db.orders.aggregate([ {$group:{_id:'$customer',count:{$sum:1},total:{$sum:'$price'}}} ]) |
| Sort by group column | Order.group('customer', { total: { $sum: '$price' } }) .order('customer') | SELECT customer, SUM(price) AS total FROM orders GROUP BY customer ORDER BY customer | db.orders.aggregate([ {$group:{_id:'$customer',total:{$sum:'$price'}}}, {$sort:{_id:1}} ]) |
| Sort by aggregated column | Order.group('customer', { total: { $sum: '$price' } }) .order('total') | SELECT customer, SUM(price) AS total FROM orders GROUP BY customer ORDER BY total | db.orders.aggregate([ {$group:{_id:'$customer',total:{$sum:'$price'}}}, {$sort:{total:1}} ]) |
| Condition on aggregated column | Order.group('customer', { count: { $sum: 1 } }) .where({ count: { $gte: 3 } }) | SELECT customer, COUNT(*) AS count FROM orders GROUP BY customer HAVING count>=3 | db.orders.aggregate([ {$group:{_id:'$customer',count:{$sum:1}}}, {$match:{count:{$gte:3}}} ]) |
| Grouping by multiple columns | Order.group('customer date', { count: { $sum: 1 } }) | SELECT customer, date, COUNT(*) AS count FROM orders GROUP BY customer, date | db.orders.aggregate([ {$group:{_id:{customer:'$customer', date:'$date'},count:{$sum:1}}} ]) |
| Min/Max functions | Order.group('customer', { min_price: { $min: '$price' }, max_price: { $max: '$price' } }) | SELECT customer, MIN(price) AS min_price, MAX(price) AS max_price FROM orders GROUP BY customer | db.orders.aggregate([ {$group:{_id:'$customer',min_price:{$min:'$price'},max_price:{$max:'$price'}}} ]) |