SQL Example |
MongoDB Example |
Description |
SELECT COUNT (*) as Count from Orders
|
Db.orders.aggregate ([{$group: {_id:null, Count: {$sum: 1}}])
|
Count All Records Fromorders |
SELECT SUM (Price) as Total from Orders
|
Db.orders.aggregate ([{$group: {_id:null, total: {$sum: "$price"}}])
|
Sum theprice field from orders, this is very useful, see the official note, said _id is necessary, but did not think can be null, |
SELECT cust_id, SUM (price) as total from orders GROUP by cust_id |
db.orders.aggregate ([ {$group: {_id: "$cust _id", total: {$sum: "$price"}}] |
for each uniquecust_id, sum the Pricefield. |
SELECT cust_id, SUM (price) as total from orders GROUP by cust_id ORDER , by total |
db.orders.aggregate ([ {$group: {_id: "$cust _id", total: {$sum: "$price"}}}, {$sort: {total:1}}]) |
for each uniquecust_id, sum of the Pricefield, results sorted by sum. |
SELECT cust_id, ord_date, &NBSP SUM (Price) , as total from orders GROUP by cust_id, ord_date |
db.orders.aggregate ([ {$group: {_id: {cust_id: "$cust _id", ; ord_date: "$ord _date"}, total: {$sum: "$p Rice "}}]) |
for each uniquecust_id,ord_dategrouping, sum the Pricefield. |
SELECT cust_id, Count (*) from orders GROUP by cust_id have Count (*) > 1 |
db.orders.aggregate ([ {$group: {_id: "$cust _id", & nbsp count: {$sum: 1}}}, {$match: {count: {$gt: 1 }}]) |
for cust_idwith multiple records, return thecust_id and the corresponding R Ecord count. |
SELECT cust_id, ord_date, &NBSP SUM (Price) , as total from orders GROUP by cust_id, ord_date have total > 250 |
db.orders.aggregate ([ {$group: {_id: {cust_id: "$cust _id", & nbsp; ord_date: "$ord _date"}, total: {$sum: "$price"}}}, {$match: {total: {$gt: 250}} }]) |
for each uniquecust_id,ord_dategrouping, sum the Pricefield and return only whe Re the sum is greater than. |
SELECT cust_id, SUM (price) as total from orders WHERE status = ' A ' GROUP by cust_id |
db.orders.aggregate ([ {$match: {status: ' A '}}, {$group: {_id: "$cust _id", &N Bsp total: {$sum: "$price"}}]) |
for each uniquecust_id with status A, sum the Pricefield. |
SELECT cust_id, SUM (price) as total from orders WHERE status = ' A ' GROUP by cust_id have total > 250 |
db.orders.aggregate ([ {$match: {status: ' A '}} , {$group: {_id: "$cust _id", total: {$sum: "$price"}}}, {$match: {total: {$gt: +}}]) |
for each UNIQUECU st_id with status A, sum the Pricefield and return only where the sum is greater than. |
SELECT cust_id, SUM (Li.qty) as qty from orders o, order_ LineItem li WHERE li.order_id = o.id GROUP by cust_id |
db.orders.aggregate ([ {$unwind: "$items"}, {$group: {_id: "$cust _id", qty: {$sum: "$i Tems.qty "}}]) |
for each uniquecust_id, sum of the corresponding line item Qtyfields associated with the orders. |
SELECT COUNT (*) from ( SELECT cust_id, ord_date from orders GROUP by cust_id, ord_date) as derivedtable |
db.orders.aggregate ([ {$group: {_id: {cust_id: "$cust _id", ord_date: "$ord _ Date "}}}, {$group: {_id:null, Count: {$sum: 1}}]) |