mongodb query vs mysql

Question

I have the following MySQL query in which I have done sum profit which I have fields like: rate, credit(money)

SELECT SUM((credit*(100-rate))/100) FROM roznamcha WHERE  (accountNum=$id AND rate!=0.0)'

I have written the following query in mongodb using node.js but it returns null whoever I have some data in my database

    const profit=await roznamcha.aggregate([  
        {
            $match:{
                rate:{$ne:0}
            }
        },
        {
            $group:{
                _id :'$accountNum',
            }
        },
        {
        $addFields:{
                resultMultiply:{
                   $divide:[
                       {$multiply:['$credit','$rate-$100']},100
                   ]

                },
    sumcredit:{
        $sum:'$resultMultiply'
    }
         } }
        
    ])
    res.status(201).json({
        status:'success',
       
        data:{
           profit
        }
    })

My output:

    {
    "status": "success",
    "data": {
        "profit": [
            {
                "_id": "612deac8fbc8ef21a0fa4ea7",
                "resultMultiply": null,
                "sumcredit": 0
            },
            {
                "_id": "612223327e2af83a4cec1272",
                "resultMultiply": null,
                "sumcredit": 0
            }
        ]
    }

my schema:

const mongoose = require('mongoose');

const roznamchaSchem=mongoose.Schema({
    accountNum: {
        type:mongoose.Schema.ObjectId,
        ref:'account',
        required: ['please specify this record is from who', true],
    },
    credit: {
        type: Number,
        default: 0
    },
    debit: {
        type: Number,
        default: 0
    },
    rate: {
        type: Number,
        default: 0
    },
description:{
        type:String,
        required:['description can not be empty',true],
        minlength: 8
    },
    issueDate:{
            type: Date,
            required:['add an valide date',true]
}
});
roznamchaSchem.index({accountNum:-1});
const Roznamcha=mongoose.model('roznamcha',roznamchaSchem);
module.exports=Roznamcha;

and my example of document:

id:612f533e8eb5533f303966e4
credit:50
debit:0
rate:2
accountNum:612deac8fbc8ef21a0fa4ea7
description:"this it for you"
issueDate:6543-01-01T00:00:00.000+00:00

can anyone guide me in solving this query?

Can you add your schema model and a sample of document?

NeNaD
– NeNaD

2021-09-01 08:55:57 +00:00
Commented Sep 1, 2021 at 8:55 — NeNaD
– NeNaD, Commented Sep 1, 2021 at 8:55

Yong Shun · Accepted Answer · 2021-09-01 10:54:15Z

2

Besides @Joe and @Nenad answer the error for the subtraction: '$rate-$100';

You need to re-position your logic structure.

Perform calculation: SUM((credit*(100-rate))/100).
Group by $accountNum and aggregate SUM for resultMultiply.

db.collection.aggregate([
  {
    $match: {
      rate: {
        $ne: 0
      }
    }
  },
  {
    $addFields: {
      resultMultiply: {
        $divide: [
          {
            $multiply: [
              "$credit",
              {
                "$subtract": [
                  100,
                  "$rate"
                ]
              }
            ]
          },
          100
        ]
      }
    }
  },
  {
    $group: {
      _id: "$accountNum",
      total: {
        $sum: "$resultMultiply"
      }
    }
  }
])

Output

[
  {
    "_id": 2,
    "total": 1.5
  },
  {
    "_id": 1,
    "total": 5.5
  }
]

Sample MongoDB playground

edited Sep 1, 2021 at 10:54

answered Sep 1, 2021 at 10:43

Yong Shun

54.5k6 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

maliha arash Over a year ago

why i can not add the $sum in $addfiled stage?

Yong Shun Over a year ago

Because as your MongoDB query, without doing sum aggregate in group stage, you will not able to sum the resultMultiply by accountNum.

Joe · Accepted Answer · 2021-09-01 09:04:56Z

2

'$rate-$100' is referring to a field named "rate-$100". You probably meant to subtract using

{$subtract: [ 100, "$rate"]}

answered Sep 1, 2021 at 9:04

Joe

29k4 gold badges25 silver badges50 bronze badges

Comments

NeNaD · Accepted Answer · 2021-09-01 09:05:27Z

1

You can not use mathematical operator minus for substraction, you have to use $subtract aggregation operator.

resultMultiply: {
  $divide: [{
      $multiply: [
        "$credit",
        { $subtract: [ 100, "$rate" ] }
      ]
    },
    100
  ]
}

answered Sep 1, 2021 at 9:05

NeNaD

20.8k11 gold badges64 silver badges118 bronze badges

3 Comments

maliha arash Over a year ago

i change minus to $subtract but i still return null

NeNaD Over a year ago

Can you add the Schema Model and example of the document in your question?

maliha arash Over a year ago

i edit my question and also i want to say i think it is problem with my variable like credit and rate when i change my variable with numbers it gave me a valid out put

Collectives™ on Stack Overflow

mongodb query vs mysql

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related