# Sum up values based on date

**URL:** <https://discourse.nodered.org/t/sum-up-values-based-on-date/57235>\
**Category:** General\
**Created:** [26 January 2022 04:27 UTC](https://discourse.nodered.org/t/sum-up-values-based-on-date/57235 "2022-01-26T04:27:20Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![mfassier](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mfassier/32/48013_2.png) [@mfassier](https://discourse.nodered.org/u/mfassier)\
**Post date:** [26 January 2022 04:27 UTC](https://discourse.nodered.org/t/sum-up-values-based-on-date/57235/1 "2022-01-26T04:27:20Z")

</div>

Hi. A simple issue that eludes my meager knowledge: User inputs a number (egg count). The input is timestamped and is recalled for display in a chart (Show how many eggs for the last 10 days, per day). This works well, as per below, with the caveat that if I input the count 2 or 3 times in a day, the chart won't sum it up. It will display it as each entry. Can someone assist with the mod needed in the function to sum up all results from the same day ? (e.g. in the morning, I input 9. In the afternoon, I input 5. The chart should show 14 for today. Not 9, then 4, with the same date).

```auto
[{"id":"5d4be9cd3f2fb5a7","type":"group","z":"1be556fa6eb0cb07","name":"Eggs graphics","style":{"label":true},"nodes":["24c58de4247bee58","d8b58e7bb50780ea","3947e94b6414de44","600fa4ed171d49f1","6ec0233fa9f2c446"],"x":974,"y":39,"w":822,"h":82},{"id":"24c58de4247bee58","type":"function","z":"1be556fa6eb0cb07","g":"5d4be9cd3f2fb5a7","name":"Fetch Eggs","func":"msg.topic=\"SELECT * FROM `eggsrecord` WHERE date between date_sub(now(),INTERVAL 1 WEEK) and now() ORDER BY date\";\nmsg.payload=[msg.payload];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1270,"y":80,"wires":[["6ec0233fa9f2c446"]]},{"id":"d8b58e7bb50780ea","type":"link out","z":"1be556fa6eb0cb07","g":"5d4be9cd3f2fb5a7","name":"EggCount","links":["d9f75986.4bd638","254a1bc5.a62c34"],"x":1755,"y":80,"wires":[]},{"id":"3947e94b6414de44","type":"inject","z":"1be556fa6eb0cb07","g":"5d4be9cd3f2fb5a7","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"900","crontab":"","once":true,"onceDelay":"5","topic":"","payload":"","payloadType":"date","x":1090,"y":80,"wires":[["24c58de4247bee58"]]},{"id":"600fa4ed171d49f1","type":"function","z":"1be556fa6eb0cb07","g":"5d4be9cd3f2fb5a7","name":"","func":"var data = [];\nvar date = [];\nmsg.payload.forEach(function(value) {\n data.push(value['EggQty']);\n date.push(value['Date']);\n});\n\nmsg.payload = [{\n series: \"\",\n data: [data],\n labels: date\n}];\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1580,"y":80,"wires":[["d8b58e7bb50780ea","9a0a1cc9145c9920"]]},{"id":"6ec0233fa9f2c446","type":"mysql","z":"1be556fa6eb0cb07","g":"5d4be9cd3f2fb5a7","mydb":"dc5d34f2.4b5a38","name":"","x":1430,"y":80,"wires":[["600fa4ed171d49f1"]]},{"id":"dc5d34f2.4b5a38","type":"MySQLdatabase","name":"","host":"192.168.2.10","port":"3306","db":"eggs","tz":"UTC","charset":"UTF8"}]

```

Any insight is appreciated.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [26 January 2022 10:30 UTC](https://discourse.nodered.org/t/sum-up-values-based-on-date/57235/2 "2022-01-26T10:30:34Z")

</div>

Maybe this will help you

> **[SQL Server SUM() Function](https://www.w3schools.com/sql/func_sqlserver_sum.asp)**
>
> W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

---

<div class="post-metadata">

**Author:** ![mfassier](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mfassier/32/48013_2.png) [@mfassier](https://discourse.nodered.org/u/mfassier)\
**Post date:** [26 January 2022 14:04 UTC](https://discourse.nodered.org/t/sum-up-values-based-on-date/57235/3 "2022-01-26T14:04:02Z")

</div>

Thanks, I'm familiar with this resource and with the function. What I'm saying is I can't get it to work as I need or rather, I find no satisfactory outcome using it. So I'll rephrase my question: should I be looking at the SQL end of things (which currently work correctly) or should I be looking at a function in Node Red to create the array as I need it to be?  
I know this should be simple, I'm not sure why I can't seem to fix it.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [26 January 2022 14:32 UTC](https://discourse.nodered.org/t/sum-up-values-based-on-date/57235/4 "2022-01-26T14:32:22Z")

</div>

What datatype is your date field?

Something like this (leaving out the where clause) ? :  
**select date, sum(EggQty) from eggsrecord group by date order by date**  
or if it's a datetime  
**select date(date), sum(EggQty) from eggsrecord group by date(date) order by date(date)**

---

<div class="post-metadata">

**Author:** ![mfassier](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mfassier/32/48013_2.png) [@mfassier](https://discourse.nodered.org/u/mfassier)\
**Post date:** [26 January 2022 15:31 UTC](https://discourse.nodered.org/t/sum-up-values-based-on-date/57235/5 "2022-01-26T15:31:50Z")

</div>

It's a datetime. This request will complete with the same result as the one I have above: that is, if I have 2 rows for 01/24, say row one has EggQty of 4 and row two has EggQty of 10, it will return 4 and 10 separatly, leading the chart to plot two columns for the same day. I want it to return 14 for that day. I'm still not convinced this operation should be done in the SQL query ? Shouldn't it be in the function where I format the message for the chart that I sum up these two ?

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [26 January 2022 15:43 UTC](https://discourse.nodered.org/t/sum-up-values-based-on-date/57235/6 "2022-01-26T15:43:01Z")

</div>

> [@mfassier](#):
>
> This request will complete with the same result as the one I have above

Does it? Post the result of the two queries.

Here's an example from my db:

```auto
select date(ontime) from socketevent 
where date(ontime)> '2022-01-19' 
order by date(ontime);
+--------------+
| date(ontime) |
+--------------+
| 2022-01-20 |
| 2022-01-20 |
| 2022-01-20 |
| 2022-01-21 |
| 2022-01-21 |
| 2022-01-22 |
| 2022-01-22 |
| 2022-01-22 |
| 2022-01-22 |
| 2022-01-23 |
| 2022-01-23 |
| 2022-01-23 |
| 2022-01-24 |
| 2022-01-24 |
| 2022-01-24 |
| 2022-01-25 |
| 2022-01-26 |
+--------------+
17 rows in set (0.011 sec)
select date(ontime), count(*) from socketevent 
where date(ontime)> '2022-01-19' 
group by date(ontime) 
order by date(ontime);
+--------------+----------+
| date(ontime) | count(*) |
+--------------+----------+
| 2022-01-20 | 3 |
| 2022-01-21 | 2 |
| 2022-01-22 | 4 |
| 2022-01-23 | 3 |
| 2022-01-24 | 3 |
| 2022-01-25 | 1 |
| 2022-01-26 | 1 |
+--------------+----------+
7 rows in set (0.010 sec)

```

> [@mfassier](#):
>
> Shouldn't it be in the function where I format the message for the chart that I sum up these two ?

You can do it either way.

---

<div class="post-metadata">

**Author:** ![mfassier](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/mfassier/32/48013_2.png) [@mfassier](https://discourse.nodered.org/u/mfassier)\
**Post date:** [26 January 2022 15:51 UTC](https://discourse.nodered.org/t/sum-up-values-based-on-date/57235/7 "2022-01-26T15:51:03Z")

</div>

So, your suggestion did make me stumble on the real problem: my misunderstanding of the argument DATE to truncate a datetime vs my column label (Date). Your example pointed straight to it and once it's taken into account, the correct query is  
**select date(Date),sum(EggQty) from eggsrecord  
WHERE date(Date) between date\_sub(now(),INTERVAL 1 WEEK) and now() group by date(Date) order by date(Date)**  
and the end result is exactly what I need. Thanks for your help.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [9 February 2022 15:52 UTC](https://discourse.nodered.org/t/sum-up-values-based-on-date/57235/8 "2022-02-09T15:52:02Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.