Sum up values based on date

General
1

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).

[{"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.

2

Maybe this will help you

3

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.

4

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)

5

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 ?

6

Does it? Post the result of the two queries.

Here's an example from my db:

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)

You can do it either way.

1 Like
7

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.

2 Likes
8

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