getting NaN value some times and some times not

Question

I get NaN value when I try to show the value of (item.total_amount,item.price,item.addtional_price) in ejs page but when I debug the variables some time I get the value and some time not!! this is the nodejs code

const getCustomers = async (req) => {
    const name = req.query.name || null;
  
    try {
      let query = `
        SELECT 
          customers.id AS customer_id,
          customers.name AS customer_name,
          customers.phone AS customer_phone,
          SUM(receipt_vouchers.amount) AS total_amount,
          reservations.price,
          reservations.addtional_price
        FROM 
          customers
        LEFT JOIN 
          receipt_vouchers 
          ON receipt_vouchers.customer_id = customers.id
        LEFT JOIN 
          reservations 
          ON reservations.customer_id = customers.id
      `;
  
      let params = [];
  
      // If a name is provided, add a WHERE clause to filter by customer name
      if (name) {
        query += ` WHERE customers.name ILIKE $1`;
        params.push(`%${name}%`);
      }
  
      // Group by customer and reservation columns to avoid aggregation on reservations values
      query += `
        GROUP BY 
          customers.id, 
          customers.name, 
          customers.phone, 
          reservations.price, 
          reservations.addtional_price
      `;
  
      // Execute the query
      const result = await db.query(query, params);
      return result.rows;
    } catch (error) {
      console.error("Error fetching customers:", error);
      throw error;
    }
  };


app.get("/customers", async (req, res) => {
    try {
        const customers = await getCustomers(req);
        console.log(customers.map(row => row));
        res.render("customers.ejs", { // Ensure the template path is correct
            data: customers
        });
    } catch (error) {
        console.error("Error in /customers route:", error); // Debugging
        res.status(500).json({ error: "Internal Server Error" }); // Handle errors
    }
});

and this is the ejs part

<tbody id="tableBody">
          <% if (data.length > 0) { %>
            <% data.forEach(function(item) {
                console.log("total_amount:", item.total_amount);
                console.log("price:", item.price);
                console.log("additional_price:", item.additional_price);
                let theAmount = parseInt(item.total_amount);
                let thePrice = parseInt(item.price) ;
                let theAdditionalPrice = parseInt(item.additional_price);

                 let total = thePrice + theAdditionalPrice;
                 let balance = total - theAmount;
            %>
              <tr>
                <td><%= item.customer_id %></td>
                <td><%= item.customer_name %></td>
                <td><%= item.customer_phone %></td>
                <td><%= item.total_amount %></td>
                <td><%= total %></td>
                <td><%= balance %></td>

and this is the value of debugging

total_amount: null
price: null
additional_price: undefined
total_amount: null
price: null
additional_price: undefined
total_amount: 322523.00
price: 2938429
additional_price: undefined
total_amount: null
price: null
additional_price: undefined
total_amount: 3999.00
price: null
additional_price: undefined
total_amount: 83834.00
price: 39382
additional_price: undefined
total_amount: 2000.00
price: null
additional_price: undefined
total_amount: 10000.00
price: 100000
additional_price: undefined
total_amount: null
price: null
additional_price: undefined

some time is get the value and some time not !!!

I try to change the data type coz I got the data from data base so I change to parseInt & parseFloat but i still getting the same value Nan I asked AI to solve this problem it suggest to add ( parseFloat(item.additional_price)||0 ) but this will give me a wrong value (0) while I want a real value (not 0)

Did you tried that query directly on sql server and confirmed that all data shown? Try to debug and set break point inside that query and check that item object actually contains those values, when you receive them. — maximelian1986
– maximelian1986, Commented Feb 19 at 9:02
I see you are doing a customers left join reservations and using reservations columns in the select clause instead of grouping functions. That means that you are retrieving customers despite having or not matching joining records on the other tables and anyway you are having multiple records for the same customer based on how many reservations it has (this part might be not an error). Anyway the left join from customers it's enough to have null values for columns not belonging to the customers table itself. Using inner join would quickly make it behave differently. — Diego D
– Diego D, Commented Feb 19 at 9:05
I am assuming the typo "addtional_price" is not really in your code?! Since you're using LEFT JOIN, some customers might not have matching reservations or receipts, causing NULL values in price, additional_price, or total_amount. Also you can ensure zero values (as you got suggested) in any case by using let xyz = parseFloat(item.xyz) || 0; — Ian Carter
– Ian Carter, Commented Feb 19 at 14:16

Burhan Shaikh · Accepted Answer · 2025-02-19 13:35:43Z

1

item.additional_price can be sometimes undefined. Also, sometimes price and total_amount can be null, which leads to NaN when performing arithmetic. Instead of parseInt(item.price), first check if the value exists.

<% 
    let theAmount = item.total_amount !== null ? parseFloat(item.total_amount) : null;
    let thePrice = item.price !== null ? parseFloat(item.price) : null;
    let theAdditionalPrice = item.additional_price !== null ? parseFloat(item.additional_price) : null;

    let total = (thePrice !== null && theAdditionalPrice !== null) ? thePrice + theAdditionalPrice : null;
    let balance = (total !== null && theAmount !== null) ? total - theAmount : null;
%>
<tr>
    <td><%= item.customer_id %></td>
    <td><%= item.customer_name %></td>
    <td><%= item.customer_phone %></td>
    <td><%= theAmount !== null ? theAmount : "N/A" %></td>
    <td><%= total !== null ? total : "N/A" %></td>
    <td><%= balance !== null ? balance : "N/A" %></td>
</tr>

answered Feb 19 at 13:35

Burhan Shaikh

413 bronze badges

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

1 Comment

Ali Alariqi Feb 19 at 13:50

Thanks for that but I still in the same point now I got this output ( ** N/A N/A** )

Ian Carter · Accepted Answer · 2025-02-19 14:25:21Z

The query is flaky:

Since you are joining customers with both receipt_vouchers and reservations, the SUM(receipt_vouchers.amount) can be incorrect because it might duplicate rows when a customer has multiple reservations.

This means total_amount might be higher than expected or NULL in some cases.

SELECT 
  customers.id AS customer_id,
  customers.name AS customer_name,
  customers.phone AS customer_phone,
  COALESCE(receipts.total_amount, 0) AS total_amount,
  reservations.price,
  reservations.additional_price
FROM 
  customers
LEFT JOIN 
  (SELECT customer_id, SUM(amount) AS total_amount FROM receipt_vouchers GROUP BY customer_id) AS receipts
  ON receipts.customer_id = customers.id
LEFT JOIN 
  reservations 
  ON reservations.customer_id = customers.id

Sachinda Nirmal · Accepted Answer · 2025-02-21 04:36:56Z

0

I have a table with price column is integer and additional_price column is numeric type . when i get sum of this two column getting NaN

edited Feb 21 at 4:36

Sachinda Nirmal

1

answered Feb 20 at 21:11

Ali Alariqi

91 silver badge2 bronze badges

Collectives™ on Stack Overflow

getting NaN value some times and some times not

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related