# Influxdb Query -

**URL:** <https://discourse.nodered.org/t/influxdb-query/49158>\
**Category:** General\
**Created:** [1 August 2021 11:40 UTC](https://discourse.nodered.org/t/influxdb-query/49158 "2021-08-01T11:40:17Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![vikozo](https://avatars.discourse-cdn.com/v4/letter/v/858c86/32.png) [@vikozo](https://discourse.nodered.org/u/vikozo)\
**Post date:** [1 August 2021 11:40 UTC](https://discourse.nodered.org/t/influxdb-query/49158/1 "2021-08-01T11:40:17Z")

</div>

Hello  
this is a query -

SELECT Tn20GT  
from  
(SELECT COUNT(average) as Tn20GT  
FROM spez\_days  
WHERE average \>=20  
AND time \> now()-12h)  
where Tn20GT\>1

basicly it should check if in the last 12h the temperatur (average) was about 20 degree!  
if it is below 20 the following switch Node will drop it.  
But it is not working properly

---

<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:** [1 August 2021 12:52 UTC](https://discourse.nodered.org/t/influxdb-query/49158/2 "2021-08-01T12:52:47Z")

</div>

You have two “where” clauses

What is the schema for that database?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [1 August 2021 20:41 UTC](https://discourse.nodered.org/t/influxdb-query/49158/3 "2021-08-01T20:41:16Z")

</div>

With complex queries like this the best plan is to work from the inside out. So first get your inner select working. If it doesn't immediately work then simplify it initially and then build it up.

> [@vikozo](#):
>
> it should check if in the last 12h the temperatur (average) was about 20 degree

I don't understand why you have the outer query. Why don't you just use the inner query and test that in the flow?

What has this go to do with node-red?

---

<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:** [1 August 2021 22:52 UTC](https://discourse.nodered.org/t/influxdb-query/49158/4 "2021-08-01T22:52:39Z")

</div>

It seems to me that if there are no rows where the average is equal or above 20, the inner select will return nothing and the outer 'where' clause would cause nothing to be return ie 0 rows returned

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [2 August 2021 11:24 UTC](https://discourse.nodered.org/t/influxdb-query/49158/5 "2021-08-02T11:24:47Z")

</div>

Yes, I think the inner will return zero or a value and the outer will return 0 or 1 rows. So it is pointless using the outer select. Just use the inner and test for zero in node-red.

---

<div class="post-metadata">

**Author:** ![vikozo](https://avatars.discourse-cdn.com/v4/letter/v/858c86/32.png) [@vikozo](https://discourse.nodered.org/u/vikozo)\
**Post date:** [3 August 2021 05:40 UTC](https://discourse.nodered.org/t/influxdb-query/49158/6 "2021-08-03T05:40:10Z")

</div>

Hello @Colin @zenofmud  
for your feedbacks, yes has nothing to do with node red. except it have this sql in a Node.

removing the outer select do not change it.

basicly if the last 12h the temperature was allways \>= 20 degree it is OK, i one drops below this it is not OK.  
If it is OK it will push another query to calculate the mean temperature over the last 12h and tell with a telegram.

have a nice day and Merci for your help  
vinc

---

<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:** [3 August 2021 08:50 UTC](https://discourse.nodered.org/t/influxdb-query/49158/7 "2021-08-03T08:50:46Z")

</div>

You have not shared the structure of your database table, but does this work?

Assuming that "average" is the name of the field holding your temperature (I don't think that is a good choice of field name!) :  
SELECT MAX(average) as Tmax, AVG(average) as Tavg FROM spez\_days WHERE time \> now()-12h  
It will return 1 row. (or zero if there is no data)

A Switch node to test if msg.payload[0].Tmax \>= 20  
Then send msg.payload[0].Tavg to Telegram

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [3 August 2021 09:09 UTC](https://discourse.nodered.org/t/influxdb-query/49158/8 "2021-08-03T09:09:09Z")

</div>

> [@vikozo](#):
>
> removing the outer select do not change it.

Show us the single query that does not work, and tell us what you mean by does not work. What does it show when you feed the result into a debug node?

---

<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:** [2 October 2021 09:09 UTC](https://discourse.nodered.org/t/influxdb-query/49158/9 "2021-10-02T09:09:35Z")

</div>

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