Menu
How could the following SQL query be converted for Mongo:
SELECT KeyID, SUM(Qty)
FROM Table
WHERE Date <= '1998-12-01'
476
An aggregate function can be used in a WHERE clause only if that clause is part of a subquery of a HAVING clause and the column name specified in the expression is a correlated reference to a group.
You cannot use aggregate functions in a WHERE clause or in a JOIN condition. However, a SELECT statement with aggregate functions in its SELECT list often includes a WHERE clause that restricts the rows to which the aggregate is applied.
GROUP BY Clause is utilized with the SELECT statement. GROUP BY aggregates the results on the basis of selected column: COUNT, MAX, MIN, SUM, AVG, etc. GROUP BY returns only one result per group of data. GROUP BY Clause always follows the WHERE Clause.
Aggregate functions cannot be used in a WHERE clause. Its violation will produce the Oracle ORA-00934 group function is not allowed here error message.
Something like:
db.myTable.aggregate([
{ $match: { mydate: { $lte: new Date('12/01/1998') } } },
{ $group: {
_id: "$keyid",
totalqty: { $sum: "$qty" }
}}
])
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
