# MySQL COUNT with Node-Red

**URL:** <https://discourse.nodered.org/t/mysql-count-with-node-red/82832>\
**Category:** General\
**Created:** [13 November 2023 15:39 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832 "2023-11-13T15:39:28Z")\
**Posts on this page:** 15\
**Page:** 2

<div class="post-metadata">

**Author:** ![PhilLu1](https://avatars.discourse-cdn.com/v4/letter/p/b19c9b/32.png) [@PhilLu1](https://discourse.nodered.org/u/PhilLu1)\
**Post date:** [16 November 2023 10:30 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/21 "2023-11-16T10:30:10Z")

</div>

Thank you anka001, but not beter..

---

<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:** [16 November 2023 10:39 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/22 "2023-11-16T10:39:17Z")

</div>

> [@jbudd](#):
>
> Try running this flow.

Did you try the flow I posted above?

---

<div class="post-metadata">

**Author:** ![PhilLu1](https://avatars.discourse-cdn.com/v4/letter/p/b19c9b/32.png) [@PhilLu1](https://discourse.nodered.org/u/PhilLu1)\
**Post date:** [16 November 2023 11:47 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/23 "2023-11-16T11:47:13Z")

</div>

Finaly, even a simple 'COUNT' never works ☹

---

<div class="post-metadata">

**Author:** ![PhilLu1](https://avatars.discourse-cdn.com/v4/letter/p/b19c9b/32.png) [@PhilLu1](https://discourse.nodered.org/u/PhilLu1)\
**Post date:** [16 November 2023 11:55 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/24 "2023-11-16T11:55:58Z")

</div>

How to integrate that MySQL perfectly working Query into Node-Red???

```auto
SELECT COUNT(*) FROM `loinc_be_fr_linguisticvariant` WHERE `SYSTEM`LIKE 'Sang' AND `CLASS` LIKE 'Chimie' AND `SCALE_TYP` LIKE 'Quantitatif' AND `TIME_ASPCT`LIKE 'Temps ponctuel'

```

---

<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:** [16 November 2023 12:03 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/25 "2023-11-16T12:03:15Z")

</div>

I'm not offering any more assistance here.

---

<div class="post-metadata">

**Author:** ![PhilLu1](https://avatars.discourse-cdn.com/v4/letter/p/b19c9b/32.png) [@PhilLu1](https://discourse.nodered.org/u/PhilLu1)\
**Post date:** [16 November 2023 12:39 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/26 "2023-11-16T12:39:06Z")

</div>

I just ask a simple solution... 🙂

---

<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:** [17 November 2023 09:35 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/27 "2023-11-17T09:35:43Z")

</div>

> [@PhilLu1](#):
>
> I just ask a simple solution.

@jbudd asked if you have tried the flow he posted. You have not answered that.

---

<div class="post-metadata">

**Author:** ![isabellatipa](https://avatars.discourse-cdn.com/v4/letter/i/ebca7d/32.png) [@isabellatipa](https://discourse.nodered.org/u/isabellatipa)\
**Post date:** [17 November 2023 09:51 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/28 "2023-11-17T09:51:00Z")

</div>

> [@PhilLu1](#):
>
> Hello,  
> I try to use COUNT with Node-Red on a MySQL DB.  
> Query is perfectly working on MySQL PHP My Admin...
> 
> ```auto
> SELECT COUNT(*) AS nombre FROM `loinc_be_fr_linguisticvariant` WHERE `SYSTEM` LIKE 'Isolat' AND `CLASS` LIKE 'Microbiologie' AND `SCALE_TYP` LIKE 'Nominal' AND `TIME_ASPCT` LIKE 'Temps ponctuel';
> 
> ```
> 
> return:  
> nombre 96
> 
> ... but not with Node-Red 😑
> 
> This is the code:
> 
> ```auto
> var value1 = msg.payload[0].SYSTEM; 
> var value2 = msg.payload[1].CLASS; 
> var value3 = msg.payload[2].SCALE_TYP; 
> var value4 = msg.payload[3].TIME_ASPCT; 
> msg.topic = 'SELECT COUNT(*) AS nombre FROM `loinc_be_fr_linguisticvariant` WHERE `SYSTEM` LIKE "' + value1 + '" AND `CLASS` LIKE "' + value2 + '"AND `SCALE_TYP` LIKE "' + value3 + '"AND `TIME_ASPCT` LIKE "' + value4 + '"; '; 
> return msg;
> 
> ```
> 
> Returned message is an Array[4] and not a number of records !  
> Thanks for your help ! 😉  
> Phil.

It looks like you are constructing a SQL query dynamically in Node-RED and trying to count the number of records based on certain conditions. The issue you're facing might be related to the way you're constructing the SQL query or processing the result in Node-RED.

Here's a revised version of your Node-RED function:  
// Extract values from the incoming payload  
var value1 = msg.payload[0].SYSTEM;  
var value2 = msg.payload[1].CLASS;  
var value3 = msg.payload[2].SCALE\_TYP;  
var value4 = msg.payload[3].TIME\_ASPCT;

// Construct the SQL query  
var sqlQuery = 'SELECT COUNT(\*) AS nombre FROM `loinc_be_fr_linguisticvariant` WHERE ';  
sqlQuery += '`SYSTEM` LIKE "' + value1 + '" AND ';  
sqlQuery += '`CLASS` LIKE "' + value2 + '" AND ';  
sqlQuery += '`SCALE_TYP` LIKE "' + value3 + '" AND ';  
sqlQuery += '`TIME_ASPCT` LIKE "' + value4 + '"';

// Set the topic to the SQL query for reference  
msg.topic = sqlQuery;

// Return the modified message object  
return msg;

---

<div class="post-metadata">

**Author:** ![PhilLu1](https://avatars.discourse-cdn.com/v4/letter/p/b19c9b/32.png) [@PhilLu1](https://discourse.nodered.org/u/PhilLu1)\
**Post date:** [17 November 2023 10:45 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/29 "2023-11-17T10:45:53Z")

</div>

Thank you isabelletipa 😉  
This return in debug:

```auto
17/11/2023 11:39:38node: debug 40
SELECT COUNT(*) AS nombre FROM loinc_be_fr_linguisticvariant WHERE SYSTEM LIKE "Urine" AND CLASS LIKE "Chimie" AND SCALE_TYP LIKE "Quantitatif" AND TIME_ASPCT LIKE "Temps ponctuel" : msg.payload : array[4]
array[4]
0: object
SYSTEM: "Urine"
1: object
CLASS: "Chimie"
2: object
SCALE_TYP: "Quantitatif"
3: object
TIME_ASPCT: "Temps ponctuel"

```

... but nothing about the COUNT ☹  
... and the UI\_text is empty...

---

<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:** [17 November 2023 11:48 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/30 "2023-11-17T11:48:00Z")

</div>

What happens if you remove the `AS nombre` clause?  
What happens if you also remove the WHERE clause?

---

<div class="post-metadata">

**Author:** ![PhilLu1](https://avatars.discourse-cdn.com/v4/letter/p/b19c9b/32.png) [@PhilLu1](https://discourse.nodered.org/u/PhilLu1)\
**Post date:** [17 November 2023 12:06 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/31 "2023-11-17T12:06:10Z")

</div>

Same... ☹

---

<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:** [17 November 2023 13:10 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/32 "2023-11-17T13:10:55Z")

</div>

It can't possibly be the same when you take out the where clause.

---

<div class="post-metadata">

**Author:** ![PhilLu1](https://avatars.discourse-cdn.com/v4/letter/p/b19c9b/32.png) [@PhilLu1](https://discourse.nodered.org/u/PhilLu1)\
**Post date:** [17 November 2023 13:40 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/33 "2023-11-17T13:40:56Z")

</div>

With full code:

```auto
var value1 = msg.payload[0].SYSTEM;
var value2 = msg.payload[1].CLASS;
var value3 = msg.payload[2].SCALE_TYP;
var value4 = msg.payload[3].TIME_ASPCT;
var sqlQuery = 'SELECT COUNT(*) AS nombre FROM loinc_be_fr_linguisticvariant WHERE ';
sqlQuery += 'SYSTEM LIKE "' + value1 + '" AND ';
sqlQuery += 'CLASS LIKE "' + value2 + '" AND ';
sqlQuery += 'SCALE_TYP LIKE "' + value3 + '" AND ';
sqlQuery += 'TIME_ASPCT LIKE "' + value4 + '"';
msg.topic = sqlQuery;
return msg;

```

debug return:

```auto
SELECT COUNT(*) AS nombre FROM loinc_be_fr_linguisticvariant WHERE SYSTEM LIKE "Urine" AND CLASS LIKE "Chimie" AND SCALE_TYP LIKE "Quantitatif" AND TIME_ASPCT LIKE "Temps ponctuel" : msg.payload : array[4]
array[4]
0: object
SYSTEM: "Urine"
1: object
CLASS: "Chimie"
2: object
SCALE_TYP: "Quantitatif"
3: object
TIME_ASPCT: "Temps ponctuel"

```

... without 'AS nombre:

```auto
var value1 = msg.payload[0].SYSTEM;
var value2 = msg.payload[1].CLASS;
var value3 = msg.payload[2].SCALE_TYP;
var value4 = msg.payload[3].TIME_ASPCT;
var sqlQuery = 'SELECT COUNT(*) FROM loinc_be_fr_linguisticvariant WHERE ';
sqlQuery += 'SYSTEM LIKE "' + value1 + '" AND ';
sqlQuery += 'CLASS LIKE "' + value2 + '" AND ';
sqlQuery += 'SCALE_TYP LIKE "' + value3 + '" AND ';
sqlQuery += 'TIME_ASPCT LIKE "' + value4 + '"';
msg.topic = sqlQuery;
return msg;

```

debug return:

```auto
SELECT COUNT(*) FROM loinc_be_fr_linguisticvariant WHERE SYSTEM LIKE "Urine" AND CLASS LIKE "Chimie" AND SCALE_TYP LIKE "Quantitatif" AND TIME_ASPCT LIKE "Temps ponctuel" : msg.payload : array[4]
array[4]
0: object
SYSTEM: "Urine"
1: object
CLASS: "Chimie"
2: object
SCALE_TYP: "Quantitatif"
3: object
TIME_ASPCT: "Temps ponctuel"

```

... without WHERE:

```auto
var value1 = msg.payload[0].SYSTEM;
var value2 = msg.payload[1].CLASS;
var value3 = msg.payload[2].SCALE_TYP;
var value4 = msg.payload[3].TIME_ASPCT;
var sqlQuery = 'SELECT COUNT(*) FROM loinc_be_fr_linguisticvariant ';
sqlQuery += 'SYSTEM LIKE "' + value1 + '" AND ';
sqlQuery += 'CLASS LIKE "' + value2 + '" AND ';
sqlQuery += 'SCALE_TYP LIKE "' + value3 + '" AND ';
sqlQuery += 'TIME_ASPCT LIKE "' + value4 + '"';
msg.topic = sqlQuery;
return msg;

```

... debug return:

```auto
SELECT COUNT(*) FROM loinc_be_fr_linguisticvariant SYSTEM LIKE "Urine" AND CLASS LIKE "Chimie" AND SCALE_TYP LIKE "Quantitatif" AND TIME_ASPCT LIKE "Temps ponctuel" : msg.payload : array[4]
array[4]
0: object
SYSTEM: "Urine"
1: object
CLASS: "Chimie"
2: object
SCALE_TYP: "Quantitatif"
3: object
TIME_ASPCT: "Temps ponctuel"

```

???

---

<div class="post-metadata">

**Author:** ![PhilLu1](https://avatars.discourse-cdn.com/v4/letter/p/b19c9b/32.png) [@PhilLu1](https://discourse.nodered.org/u/PhilLu1)\
**Post date:** [17 November 2023 13:57 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/34 "2023-11-17T13:57:29Z")

</div>

I found the error 👌  
A block was not attached to another... 🥴  
Many thanks to all 😉

---

<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:** [16 January 2024 13:58 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/35 "2024-01-16T13:58:12Z")

</div>

Closed on 16 January 2024 13:58 UTC

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

[Previous page](https://discourse.nodered.org/t/mysql-count-with-node-red/82832.md?page=1)