0

i have this two collection and i want to join, after this I want to sum the value of TotalPrice each orderdate :

some line of my two collections

the first collection date :

{"Id_Date":1,"OrderDate":"2018-01-03"},
{"Id_Date":2,"OrderDate":"2018-01-05"},
{"Id_Date":3,"OrderDate":"2018-01-09"},
{"Id_Date":4,"OrderDate":"2018-01-10"},
{"Id_Date":5,"OrderDate":"2018-01-11"},
{"Id_Date":6,"OrderDate":"2018-01-12"},
{"Id_Date":7,"OrderDate":"2018-01-18"},
{"Id_Date":8,"OrderDate":"2018-01-21"},
{"Id_Date":9,"OrderDate":"2018-01-22"},
{"Id_Date":10,"OrderDate":"2018-02-02"},

the second collection order_fact :

{"orderid":"c3e15faf-e338-4ed5-9bbb-bbcd15295fd7","Id_Cust":"10995116278711","Id_Date":"91","TotalPrice":1933.53,"Orderline":[{"productId":"4165","asin":"B005G2G2SQ","title":"ESEE-6 Plain Black Blade With Grey Removable Lined Micarta Handles 1095 Carbon Steel 57-Rc","price":140.05,"brand":"Fila_(company)"},{"productId":"5916","asin":"B001CJ2LQ4","title":"Aimpoint M4s 2 Minute of Angle QRP2 CompM4 Sight with Mount","price":804.95,"brand":"Atletica"},{"productId":"7779","asin":"B003DO5L3G","title":"Lee Precision Load Master 223 Remington Reloading Rifle Kit (Red)","price":249.26,"brand":"BURRDA"},{"productId":"2722","asin":"B0017U1MJU","title":"Casio Mens PAW1500-1V Pathfinder Multi-Band Solar Atomic Ultimate Watch","price":190.27,"brand":"Reebok"},{"productId":"52","asin":"B007SYGLZO","title":"Armasight Spark CORE Multi-Purpose Night Vision Monocular","price":549.0,"brand":"Signia_(sportswear)"}],"Livreur":{"Id_livreur":"91bcee9f-8896-4e18-a24c-a8e86b97b3a1","Nom":"Tyson Anderson","gender":"Male","phone":"7-458-665-3444"}},
{"orderid":"35b26e6d-e450-4edc-9071-306cea10a083","Id_Cust":"10995116278711","Id_Date":"249","TotalPrice":1540.91,"Orderline":[{"productId":"1978","asin":"B001CZBMRC","title":"Benchmade Osborne Design Rift Knife","price":178.5,"brand":"CCM_(ice_hockey)"},{"productId":"857","asin":"B004IIKA2I","title":"Barnett Quad 400 Crossbow Package (Quiver 3 - 22-Inch Arrows and 4x32mm Scope)","price":354.99,"brand":"Fischer_(company)"},{"productId":"8362","asin":"B00635GTTW","title":"Schwinn IC2 Indoor Cycling Exercise Bike","price":422.49,"brand":"Admiral_Sportswear"},{"productId":"216","asin":"B000GKN45C","title":"Swagman XTC-4 Cross-Country 4-Bike Hitch Mount Rack (2-Inch Receiver Hitch)","price":349.99,"brand":"Elfin_Sports_Cars"},{"productId":"7766","asin":"B000KKEPJ2","title":"Lyman Reloading Press T-Mag Turret Press","price":234.94,"brand":"BURRDA"}],"Livreur":{"Id_livreur":"48d64c09-7a63-4fca-815a-a96921a82ee7","Nom":"Abdul Coll","gender":"Male","phone":"1-273-204-4721"}},
{"orderid":"ffaec62e-9875-42af-8789-0a97eabe77e4","Id_Cust":"10995116278711","Id_Date":"67","TotalPrice":1058.41,"Orderline":[{"productId":"2675","asin":"B00BPRM2O4","title":"Large Exercise Mat Large Workout Mat With A Unique Fabric Finish That Provides Excellent Traction And Floor Protection. Can Be Used In Conjunction With The Square36 YOGA Mat. The Square36 CARDIO Mat - The BIG Exercise Mat- Step Into the Square.","price":159.99,"brand":"Reebok"},{"productId":"1380","asin":"B000EQCVQ6","title":"Eureka! Apex 2XT Two-Person Tent","price":175.16,"brand":"Topper_(sports)"},{"productId":"9665","asin":"B004H4VSI8","title":"IRONMAN HIGH CAPACITY GRAVITY 3000 INVERSION TABLE","price":239.8,"brand":"Wilson_Sporting_Goods"},{"productId":"7603","asin":"B0017SC9H6","title":"Zero Tolerance Combat Folding Knife","price":199.0,"brand":"Tramontana_(sports_car)"},{"productId":"887","asin":"B00BCLL8C0","title":"SportRack SR7018 Vista XL Rear Opening Cargo Box 18-Cubic Feet","price":284.46,"brand":"Fischer_(company)"}],"Livreur":{"Id_livreur":"7548ac7b-5bf7-4c5a-a80d-5390b73b9b90","Nom":"Henry Wilde","gender":"Male","phone":"3-624-033-5243"}},
{"orderid":"125e7887-c41d-4a5a-aa6b-d2a3679d58e7","Id_Cust":"10995116278711","Id_Date":"477","TotalPrice":1624.98,"Orderline":[{"productId":"6250","asin":"B005DYQ9V2","title":"Invicta Men s 1463 Reserve Collection Chronograph Silver Dial Stainless Steel Watch","price":199.95,"brand":"Keuka_(brand)"},{"productId":"1138","asin":"B000AMRN12","title":"Stamina AeroPilates Reformer with Free-Form Cardio Rebounder","price":449.0,"brand":"Topper_(sports)"},{"productId":"7575","asin":"B005D7FXMA","title":"Cygolite Expilion 400 Usb Rechargeable Headlight with Quick Release Li-Ion Battery Stick and Wall Charger","price":113.99,"brand":"Derbi"},{"productId":"6529","asin":"B005Z2CYX4","title":"Zero Tolerance ZT0550 Hinderer Design Folding Knife","price":160.0,"brand":"CA_Sports"},{"productId":"7125","asin":"B007INYN6O","title":"Barnett Zombie 350 CRT Crossbow","price":702.04,"brand":"Elan_Snowboards"}],"Livreur":{"Id_livreur":"a1060a87-fdff-4acc-b728-c805b72b0740","Nom":"Percy Thornton","gender":"Male","phone":"5-744-522-0782"}},
{"orderid":"b259eafc-2f47-47dc-927b-5b6d6edbba1d","Id_Cust":"10995116278711","Id_Date":"617","TotalPrice":1484.76,"Orderline":[{"productId":"7667","asin":"B008W1TUAK","title":"MAGPUL ACS CARB STOCK MIL-SPEC BLACK","price":98.99,"brand":"POC_Sports"},{"productId":"6406","asin":"B001CJX50K","title":"Kinetic Rock n Roll Trainer w/Road Resistance Unit","price":389.99,"brand":"MYLAPS_Sports_Timing"},{"productId":"1883","asin":"B000O8Z7TW","title":"KNEX Education - Exploring Machines","price":126.79,"brand":"TRYMAX"},{"productId":"4158","asin":"B003AQKQAA","title":"Vortex Viper PST 6-24x50 Rifle PST-624S1-M","price":749.0,"brand":"Fila_(company)"},{"productId":"7578","asin":"B00D43A93I","title":"Under Armour Hammer 8600014-4808 Polarized Wrap Sunglasses","price":119.99,"brand":"Derbi"}],"Livreur":{"Id_livreur":"eb135eef-7ee2-4d90-b073-cbd4570935b3","Nom":"Violet Adler","gender":"Female","phone":"5-466-075-2676"}},
{"orderid":"b5755c1e-fc3f-4481-a175-edfc82290ea5","Id_Cust":"10995116278711","Id_Date":"208","TotalPrice":2487.71,"Orderline":[{"productId":"965","asin":"B000O3AVNY","title":"Advanced Elements AdvancedFrame Expedition Kayak","price":599.99,"brand":"Donnay_(sports)"},{"productId":"6888","asin":"B0029KL3S2","title":"Precor 240i Commercial Series StretchTrainer","price":704.2,"brand":"Elan_(company)"},{"productId":"6439","asin":"B004PJ1J4S","title":"Invicta Men s 6566 Subaqua Noma IV Collection Chronograph Black Polyurethane Watch","price":340.65,"brand":"MYLAPS_Sports_Timing"},{"productId":"9503","asin":"B0034PXYRY","title":"Eotech EXPS3-0 Holographic Weapon Site","price":606.92,"brand":"EA_Sports"},{"productId":"7740","asin":"B00271ERVI","title":"Crimson Trace Lasergrip for Ruger Lcr","price":235.95,"brand":"BURRDA"}],"Livreur":{"Id_livreur":"c75a672e-37cf-498e-8bf9-8dae7857165f","Nom":"Renee Eyres","gender":"Female","phone":"6-311-624-4031"}},
{"orderid":"8a129f4e-e736-4919-9a22-c98373f1df3b","Id_Cust":"10995116278711","Id_Date":"390","TotalPrice":1233.67,"Orderline":[{"productId":"1929","asin":"B004P4HH8U","title":"Barnett Ghost 350 CRT Crossbow Package (Quiver 3 - 20-Inch Arrows and Illuminated 3x32mm Scope)","price":509.99,"brand":"TRYMAX"},{"productId":"2940","asin":"B004IIZVQ8","title":"Barnett Wildcat C5 Crossbow Package (Quiver 3 - 20-Inch Arrows and Premium Red Dot Sight)","price":342.99,"brand":"Volkl"},{"productId":"3174","asin":"B006U0YZPA","title":"Bushnell X-8 6MP Trail Camera with Night Vision and Field Scan","price":136.94,"brand":"Daei_Sport"},{"productId":"6527","asin":"B003MA1SSI","title":"Sadlak Industries M14 National Match Spring Guide","price":40.75,"brand":"CA_Sports"},{"productId":"1759","asin":"B007L4ZGXE","title":"Zero Tolerance ZT0560 Hinderer Design Black Folding Knife","price":203.0,"brand":"Olympikus"}],"Livreur":{"Id_livreur":"2ccb39c1-5c1a-46d8-b394-fc887bd4cecd","Nom":"Faith Flanders","gender":"Female","phone":"2-586-775-3278"}},
{"orderid":"bb671476-5f67-414a-9b84-77498b05f0bd","Id_Cust":"10995116278711","Id_Date":"205","TotalPrice":1601.81,"Orderline":[{"productId":"810","asin":"B009K28INA","title":"Schwinn 425 Elliptical Trainer (2013)","price":599.99,"brand":"Fischer_(company)"},{"productId":"6802","asin":"B000WY8ZHO","title":"Luminox Men s Navy Seal ColorMark Watch 3051","price":229.09,"brand":"Onda_(sportswear)"},{"productId":"3178","asin":"B007O5B0LC","title":"Weslo Cadence G 5.9 Treadmill","price":351.0,"brand":"Daei_Sport"},{"productId":"2126","asin":"B0025YCYK8","title":"Spyderco Temperence 2 Canvas Micarta Plain Edge Knife","price":207.72,"brand":"Li-Ning"},{"productId":"6736","asin":"B002QWB8HY","title":"CamelBak BFM 3L Backpack","price":214.01,"brand":"Onda_(sportswear)"}],"Livreur":{"Id_livreur":"629b2edd-8bd4-4d50-8e63-33f4904ffd2c","Nom":"Stephanie Garcia","gender":"Female","phone":"6-618-288-6820"}},
{"orderid":"d3016947-f478-4782-941d-37132482a10a","Id_Cust":"10995116278711","Id_Date":"694","TotalPrice":1534.78,"Orderline":[{"productId":"3029","asin":"B00AJK9CW8","title":"Seiko Men s SRP307 Classic Automatic Dive Watch","price":192.87,"brand":"Kettler"},{"productId":"2964","asin":"B004V956P0","title":"Crosman Optimus Break Barrel Air Rifle (.177)","price":91.98,"brand":"Volkl"},{"productId":"4115","asin":"B003UNZHNY","title":"Schwinn A10 Upright Exercise Bike (2011)","price":199.99,"brand":"Fila_(company)"},{"productId":"6853","asin":"B0081MPIBA","title":"ETEK4 - Planet Eclipse Etek 4 LT / AM Paintball Guns","price":499.95,"brand":"Elan_(company)"},{"productId":"893","asin":"B001AS697O","title":"Bowflex PR1000 Home Gym","price":549.99,"brand":"Fischer_(company)"}],"Livreur":{"Id_livreur":"787192a7-8417-44a9-a5bb-dd9060afd524","Nom":"Isla Denton","gender":"Female","phone":"1-260-416-8800"}},
{"orderid":"30dd4a79-4dac-4043-8fbc-e746e78aced3","Id_Cust":"10995116278711","Id_Date":"702","TotalPrice":1329.04,"Orderline":[{"productId":"2726","asin":"B005SSWKMK","title":"Casio Men s PRW2500T-7CR Pathfinder Triple Sensor Tough Solar Digital Multi-Function Titanium Pathfinder Casual Watch","price":266.12,"brand":"Reebok"},{"productId":"6563","asin":"B0014NDXGA","title":"Bushnell Tour V2 Standard Edition Golf Laser Rangefinder","price":290.06,"brand":"Onda_(sportswear)"},{"productId":"4856","asin":"B001QD48GC","title":"FreeForm Hideaway Home Gym","price":641.93,"brand":"ASICS"},{"productId":"7653","asin":"B007ZNUG9K","title":"Blackhawk Black TraverseTrack Bipod - 10 - BH71BP10BK","price":50.98,"brand":"POC_Sports"},{"productId":"4166","asin":"B00AJT98OQ","title":"Universal Brass Catcher","price":79.95,"brand":"Fila_(company)"}],"Livreur":{"Id_livreur":"5acb6f8a-fb71-4b32-b40d-fde782d70600","Nom":"Belinda Marshall","gender":"Female","phone":"6-671-643-4273"}},

How i can sum of the total prices for each date ?

Improve this question

2 Answers 2

Reset to default
1

The below query will join both the tables and return the sum based on order date:

db.order_fact.aggregate([
  {
    "$lookup": {
      "from": "date",
      "localField": "Id_Date",
      "foreignField": "Id_Date",
      "as": "dateDetails"
    }
  },
  {
    "$unwind": "$dateDetails"
  },
  {
    "$group": {
      "_id": {
        "date": "$dateDetails.OrderDate"
      },
      "totalPrice": {
        "$sum": "$TotalPrice"
      }
    }
  }
])
Improve this answer
0

As per MongoDB documentation here Changed in version 3.2: $sum is available in the $group and $project stages. In previous versions of MongoDB, $sum is available in the $group stage only.

When used in the $group stage, $sum has the following syntax and returns the collective sum of all the numeric values that result from applying a specified expression to each document in a group of documents that share the same group by key:

{ $sum: <expression> }

For Example Use in $group Stage

Consider a sales collection with the following documents:

{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-01-01T08:00:00Z") }
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-02-03T09:00:00Z") }
{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-03T09:05:00Z") }
{ "_id" : 4, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate("2014-02-15T08:00:00Z") }
{ "_id" : 5, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T09:05:00Z") }

Grouping the documents by the day and the year of the date field, the following operation uses the $sum accumulator to compute the total amount and the count for each group of documents.

db.sales.aggregate(
   [
     {
       $group:
         {
           _id: { day: { $dayOfYear: "$date"}, year: { $year: "$date" } },
           totalAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } },
           count: { $sum: 1 }
         }
     }
   ]
)

The operation returns the following results:

{ "_id" : { "day" : 46, "year" : 2014 }, "totalAmount" : 150, "count" : 2 }
{ "_id" : { "day" : 34, "year" : 2014 }, "totalAmount" : 45, "count" : 2 }
{ "_id" : { "day" : 1, "year" : 2014 }, "totalAmount" : 20, "count" : 1 }

Using $sum on a non-existent field returns a value of 0. The following operation attempts to $sum on qty:

db.sales.aggregate(
   [
     {
       $group:
         {
           _id: { day: { $dayOfYear: "$date"}, year: { $year: "$date" } },
           totalAmount: { $sum: "$quantity" },
           count: { $sum: 1 }
         }
     }
   ]
)

The operation returns:

{ "_id" : { "day" : 46, "year" : 2014 }, "totalAmount" : 0, "count" : 2 }
{ "_id" : { "day" : 34, "year" : 2014 }, "totalAmount" : 0, "count" : 2 }
{ "_id" : { "day" : 1, "year" : 2014 }, "totalAmount" : 0, "count" : 1 }

When used in the $project stage, $sum returns the sum of the specified expression or list of expressions for each document and has one of two syntaxes:

  • $sum has one specified expression as its operand:

    { $sum: }

  • $sum has a list of specified expressions as its operand:

    { $sum: [ , ... ] }

For Example Use in $project Stage

A collection students contains the following documents:

{ "_id": 1, "quizzes": [ 10, 6, 7 ], "labs": [ 5, 8 ], "final": 80, "midterm": 75 }
{ "_id": 2, "quizzes": [ 9, 10 ], "labs": [ 8, 8 ], "final": 95, "midterm": 80 }
{ "_id": 3, "quizzes": [ 4, 5, 5 ], "labs": [ 6, 5 ], "final": 78, "midterm": 70 }

The following example uses the $sum in the $project stage to calculate the total quiz scores, the total lab scores, and the total of the final and the midterm:

db.students.aggregate([
   {
     $project: {
       quizTotal: { $sum: "$quizzes"},
       labTotal: { $sum: "$labs" },
       examTotal: { $sum: [ "$final", "$midterm" ] }
     }
   }
])

The operation results in the following documents:

{ "_id" : 1, "quizTotal" : 23, "labTotal" : 13, "examTotal" : 155 }
{ "_id" : 2, "quizTotal" : 19, "labTotal" : 16, "examTotal" : 175 }
{ "_id" : 3, "quizTotal" : 14, "labTotal" : 11, "examTotal" : 148 }

For Further you ref here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.