# 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:** 20
**Page:** 1
### Author:  [@PhilLu1](https://discourse.nodered.org/u/PhilLu1)
#### Post date: [13 November 2023 15:39 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/1 "2023-11-13T15:39:28Z")
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.
---
### Author:  [@ralphwetzel](https://discourse.nodered.org/u/ralphwetzel)
#### Post date: [13 November 2023 15:42 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/2 "2023-11-13T15:42:30Z")
> [@PhilLu1](#):
>
> ```auto
> 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 + '"; ';
>
> ```
Edit: ~~Following the Node-RED standard, I'm pretty sure, you have to put your query string into `msg.payload`.~~ Never stop learning! Thank you @jbudd ... 👍
What you received - as an Array[4] - most probably are your values of [SYSTEM, CLASS, SCALE\_TYP, TIME\_ASPCT].
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [13 November 2023 15:44 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/3 "2023-11-13T15:44:29Z")
@ralphwetzel, for node-red-node-mysql, the query has to be in msg.topic.
Hi @PhilLu1, welcome to the forum.
Can you use a debug node to show us the msg.topic passed to MySQL, also the contents of the returned array?
I would expect the result from MySQL to look like this
---
### Author:  [@PhilLu1](https://discourse.nodered.org/u/PhilLu1)
#### Post date: [13 November 2023 16:30 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/4 "2023-11-13T16:30:05Z")
... I have to receive COUNT result into a Dashboard TEXT
```auto
{{msg.topic.nombre}}
```
... but not work 😣
---
### Author:  [@PhilLu1](https://discourse.nodered.org/u/PhilLu1)
#### Post date: [13 November 2023 16:33 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/5 "2023-11-13T16:33:32Z")
yes... I receive SYSTEM, CLASS, SCALE\_TYP, TIME\_ASPCT
How receive 'nombre' from "AS nombre" ?
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [13 November 2023 16:35 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/6 "2023-11-13T16:35:45Z")
> [@PhilLu1](#):
>
> I have to receive COUNT result into a Dashboard TEXT
>
> `{{msg.topic.nombre}}`
You didn't provide the data I asked for but nevertheless I'll point out that the mysql node returns the query in msg.topic and the result in msg.payload.
---
### Author:  [@PhilLu1](https://discourse.nodered.org/u/PhilLu1)
#### Post date: [13 November 2023 16:43 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/7 "2023-11-13T16:43:25Z")
This is what the debug contains...
```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"
```
with msg.topic =
---
### Author:  [@PhilLu1](https://discourse.nodered.org/u/PhilLu1)
#### Post date: [13 November 2023 16:46 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/8 "2023-11-13T16:46:51Z")
... and
```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"; "
```
... with msg.payload =
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [13 November 2023 16:52 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/9 "2023-11-13T16:52:38Z")
> [@PhilLu1](#):
>
> ```auto
> SELECT COUNT() FROM `loinc_be_fr_lingui...
>
> ```
You are missing an asterisk in the query.
---
### Author:  [@ralphwetzel](https://discourse.nodered.org/u/ralphwetzel)
#### Post date: [13 November 2023 17:08 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/10 "2023-11-13T17:08:09Z")
@PhilLu1
> [@jbudd](#):
>
> You didn't provide the data I asked for
We need to guess too much to be efficient in supporting you.
Why don't you share your flow - at least the relevant part of it - to allow us to examine the details?
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [13 November 2023 17:23 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/11 "2023-11-13T17:23:34Z")
I would think it would be more like this
```auto
Select count(*)
From
(
SELECT *
FROM `loinc_be_fr_linguisticvariant`
WHERE `SYSTEM` LIKE "Urine" AND
`CLASS` LIKE "Chimie" AND
`SCALE_TYP` LIKE "Quantitatif" AND
`TIME_ASPCT` LIKE "Temps ponctuel"
) as nombre
```
But not a sql expert.
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [13 November 2023 17:27 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/12 "2023-11-13T17:27:40Z")
Ummm. Don't think so, sorry!
The OP has already pointed out that his query works in phpMyAdmin.
The problem in Node-red appears to be the missing asterisk in
select count(\*) from ...
---
### Author:  [@PhilLu1](https://discourse.nodered.org/u/PhilLu1)
#### Post date: [13 November 2023 18:53 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/13 "2023-11-13T18:53:49Z")
I try with and without...
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [13 November 2023 22:18 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/14 "2023-11-13T22:18:07Z")
> [@PhilLu1](#):
>
> I try with and without
If you try the version without the asterisk you should get an error.
Show us what you do get?
Try running this flow. Obviously you will need to change the db node to match your database.
Check that the three queries are all benign SELECTs before touching your live database of course.
```auto
[{"id":"bf05578e63864ed4","type":"tab","label":"Test MySQL Select","disabled":false,"info":"","env":[]},{"id":"b51cf4e87553bbce","type":"inject","z":"bf05578e63864ed4","name":"","props":[{"p":"foo","v":"select count(*) as nombre FROM loinc_be_fr_linguisticvariant","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":110,"y":40,"wires":[["7a595d649a8e609a"]]},{"id":"fcaa1d889fa0ce64","type":"mysql","z":"bf05578e63864ed4","mydb":"2381cb15.b563bc","name":"","x":670,"y":40,"wires":[["a66b312990a2ccbd"]]},{"id":"7a595d649a8e609a","type":"change","z":"bf05578e63864ed4","name":"select count(*) from","rules":[{"t":"set","p":"topic","pt":"msg","to":"select count(*) as nombre1 FROM loinc_be_fr_linguisticvariant","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":130,"y":100,"wires":[["01b3fb4a4a4596f9"]]},{"id":"db427d1ddc4a46d9","type":"change","z":"bf05578e63864ed4","name":"select count(*) from where","rules":[{"t":"set","p":"topic","pt":"msg","to":"select count(*) as nombre2 FROM loinc_be_fr_linguisticvariant WHERE SYSTEM LIKE \"Urine\"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":150,"y":160,"wires":[["ccbce48b144b7ad1"]]},{"id":"75f8d08fc4cbd614","type":"change","z":"bf05578e63864ed4","name":"select count(*) from ... where ... AND ... AND ... AND","rules":[{"t":"set","p":"topic","pt":"msg","to":"select count(*) as nombre3 FROM loinc_be_fr_linguisticvariant WHERE SYSTEM LIKE \"Urine\" AND CLASS LIKE \"Chimie\" AND SCALE_TYP LIKE \"Quantitatif\" AND TIME_ASPC LIKE \"Temps ponctuel\"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":240,"y":220,"wires":[["7b2a8af716894eb7"]]},{"id":"01b3fb4a4a4596f9","type":"link call","z":"bf05578e63864ed4","name":"","links":["accc1509a0245556"],"linkType":"static","timeout":"30","x":310,"y":100,"wires":[["db427d1ddc4a46d9","1b956ce9d671afe6"]]},{"id":"accc1509a0245556","type":"link in","z":"bf05578e63864ed4","name":"Run Query","links":[],"x":565,"y":40,"wires":[["fcaa1d889fa0ce64"]]},{"id":"a66b312990a2ccbd","type":"link out","z":"bf05578e63864ed4","name":"link out 9","mode":"return","links":[],"x":795,"y":40,"wires":[]},{"id":"ccbce48b144b7ad1","type":"link call","z":"bf05578e63864ed4","name":"","links":["accc1509a0245556"],"linkType":"static","timeout":"30","x":350,"y":160,"wires":[["75f8d08fc4cbd614","d270134f5d9dd223"]]},{"id":"7b2a8af716894eb7","type":"link call","z":"bf05578e63864ed4","name":"","links":["accc1509a0245556"],"linkType":"static","timeout":"30","x":530,"y":220,"wires":[["2a5db5fb41997a69"]]},{"id":"1b956ce9d671afe6","type":"debug","z":"bf05578e63864ed4","name":"nombre1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":480,"y":100,"wires":[]},{"id":"d270134f5d9dd223","type":"debug","z":"bf05578e63864ed4","name":"nombre2","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":520,"y":160,"wires":[]},{"id":"2a5db5fb41997a69","type":"debug","z":"bf05578e63864ed4","name":"nombre3","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":700,"y":220,"wires":[]},{"id":"2381cb15.b563bc","type":"MySQLdatabase","name":"db","host":"127.0.0.1","port":"3306","db":"espresso","tz":"","charset":"UTF8"}]
```
Expand all 3 debug outputs to look something like this, and show us. (My example queries access different fields, table and database). Also show us any errors please.
---
### Author:  [@shrickus](https://discourse.nodered.org/u/shrickus)
#### Post date: [14 November 2023 17:59 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/15 "2023-11-14T17:59:00Z")
IIRC, the SQL nodes always return an array of "results" -- usually representing rows of object data...
Since you requested just a count() of the rows, your answer is the number `4` in an array of 1 resulting row. This is working as intended -- in fact, even the SQL command line returns a record count of 1 when you ask for a simple row count.
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [14 November 2023 19:54 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/16 "2023-11-14T19:54:48Z")
Hmm. If @PhilLu1 was prepared to show us what result he/she gets we could be more certain, but msg.payload ought to contain an array with one element {nombre: nnn}
I suspect they have stopped looking at this thread.
---
### Author:  [@PhilLu1](https://discourse.nodered.org/u/PhilLu1)
#### Post date: [15 November 2023 09:06 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/17 "2023-11-15T09:06:07Z")
no, I'm there 😉
---
### Author:  [@PhilLu1](https://discourse.nodered.org/u/PhilLu1)
#### Post date: [15 November 2023 09:07 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/18 "2023-11-15T09:07:07Z")
I try also with COUNT(\*)...
---
### Author:  [@jw\_te\_r](https://discourse.nodered.org/u/jw_te_r)
#### Post date: [15 November 2023 15:04 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/19 "2023-11-15T15:04:26Z")
If I use this statement for MyData:
```auto
var newMsg = {
"topic" : "SELECT COUNT(*) as aantal FROM `MyData`;"
}
return newMsg;
```
I can read my result from payload[0].aantal.
---
### Author:  [@anka001](https://discourse.nodered.org/u/anka001)
#### Post date: [15 November 2023 15:41 UTC](https://discourse.nodered.org/t/mysql-count-with-node-red/82832/20 "2023-11-15T15:41:33Z")
> Don't know if it is the root cause, but your querystring lacks some blanks. Instead of
```auto
'"AND `SCALE_TYP` LIKE "'
```
it should be
```auto
'" AND `SCALE_TYP` LIKE "'
```
same thing here
```auto
'"AND `TIME_ASPCT` LIKE "'
```
[Next page](https://discourse.nodered.org/t/mysql-count-with-node-red/82832.md?page=2)