# MSSQL query sum value display in ui\_text

**URL:** <https://discourse.nodered.org/t/mssql-query-sum-value-display-in-ui-text/44432>\
**Category:** General\
**Created:** [20 April 2021 05:43 UTC](https://discourse.nodered.org/t/mssql-query-sum-value-display-in-ui-text/44432 "2021-04-20T05:43:55Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![sahsha](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sahsha/32/43845_2.png) [@sahsha](https://discourse.nodered.org/u/sahsha)\
**Post date:** [20 April 2021 05:43 UTC](https://discourse.nodered.org/t/mssql-query-sum-value-display-in-ui-text/44432/1 "2021-04-20T05:43:55Z")

</div>

Need help to display only numeric value. I'm getting value with brackets/special characters **[{" ":** value **}]**.

Can anyone please help.  
 ![screenshot](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/7/a7ea256e20a618e8165a10f61529ed936db425d2.png)

flow given below:

```auto
[{"id":"ff3a0683.417658","type":"MSSQL","z":"6741e784.e28a18","mssqlCN":"7aaf39f5.525778","name":"","query":"SELECT SUM([OK PRODUCTION] ) FROM dbo.SAP_SCADA_12 WHERE DATE BETWEEN '2021-04-01'AND '2021-04-30' AND [MACHINE CODE]='M06CN119'","outField":"payload","x":400,"y":120,"wires":[["797268d5.514638","9d52565b.d095d8"]]},{"id":"8010ce5d.29058","type":"inject","z":"6741e784.e28a18","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":230,"y":120,"wires":[["ff3a0683.417658"]]},{"id":"9d52565b.d095d8","type":"debug","z":"6741e784.e28a18","name":"","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":590,"y":140,"wires":[]},{"id":"797268d5.514638","type":"ui_text","z":"6741e784.e28a18","group":"b0f97496.a4d498","order":7,"width":0,"height":0,"name":"","label":"text","format":"{{msg.payload}}","layout":"row-spread","x":570,"y":100,"wires":[]},{"id":"7aaf39f5.525778","type":"MSSQL-CN","tdsVersion":"7_3_B","name":"MT06","server":"192.168.26.172","port":"1433","encyption":false,"database":"MT06","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5"},{"id":"b0f97496.a4d498","type":"ui_group","name":"testing","tab":"152250d2.cae22f","order":1,"disp":true,"width":24,"collapse":false},{"id":"152250d2.cae22f","type":"ui_tab","name":"Testing","icon":"dashboard","order":14,"disabled":false,"hidden":false}]

```

---

<div class="post-metadata">

**Author:** ![sahsha](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sahsha/32/43845_2.png) [@sahsha](https://discourse.nodered.org/u/sahsha)\
**Post date:** [20 April 2021 06:40 UTC](https://discourse.nodered.org/t/mssql-query-sum-value-display-in-ui-text/44432/2 "2021-04-20T06:40:05Z")

</div>

solved it myself 🙃

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [20 April 2021 12:06 UTC](https://discourse.nodered.org/t/mssql-query-sum-value-display-in-ui-text/44432/3 "2021-04-20T12:06:00Z")

</div>

Please share so that others can benefit as well. Thanks.

---

<div class="post-metadata">

**Author:** ![sahsha](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/sahsha/32/43845_2.png) [@sahsha](https://discourse.nodered.org/u/sahsha)\
**Post date:** [22 April 2021 17:57 UTC](https://discourse.nodered.org/t/mssql-query-sum-value-display-in-ui-text/44432/4 "2021-04-22T17:57:10Z")

</div>

@TotallyInformation what I figured by going through numerous forums was to use **AS** command in SQL query.  
 ![screenshot 1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/5/3583c527cd40ab5c64e244e84587c0a667794b92.png)

then I added a function node.  
 ![screenshot 2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/c/8c1b64a3953f6459a78a1cffda452415093a9f1a.png)

check flow given below:

```auto
[{"id":"3ef514cb.fef16c","type":"MSSQL","z":"6741e784.e28a18","g":"19628c76.b466d4","mssqlCN":"898d2a6f.9fb7b8","name":"MACHINE SHIFT TIME (X)(Min.)","query":"SELECT \nSUM([MACHINE SHIFT TIME (X)(Min.)]) \nAS runtime\nFROM dbo.SHIFT_DATA \nWHERE \ndatepart(mm,DATE) =month(getdate()) \nAND datepart(yyyy,DATE) =year(getdate()) \nAND [MACHINE CODE]='M02CN001'","outField":"payload","x":480,"y":300,"wires":[["42e425ef.16fa0c"]]},{"id":"2355426d.56cbee","type":"MSSQL","z":"6741e784.e28a18","g":"19628c76.b466d4","mssqlCN":"898d2a6f.9fb7b8","name":"AVAILABILITY LOSS TIME(Min.)","query":"SELECT \nSUM([AVAILABILITY LOSS TIME(Min.)]) \nAS avalosstime \nFROM dbo.SHIFT_DATA \nWHERE \ndatepart(mm,DATE) =month(getdate()) \nAND datepart(yyyy,DATE) =year(getdate()) \nAND [MACHINE CODE]='M02CN001'","outField":"payload","x":480,"y":340,"wires":[["601fe5cb.24124c"]]},{"id":"432295a9.4b8f1c","type":"debug","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":870,"y":260,"wires":[]},{"id":"b6b56e3c.13f4c","type":"debug","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":870,"y":360,"wires":[]},{"id":"54cafb53.6f3a44","type":"debug","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1030,"y":260,"wires":[]},{"id":"44ec235d.acc4fc","type":"debug","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1170,"y":360,"wires":[]},{"id":"42e425ef.16fa0c","type":"function","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"","func":"msg.payload = msg.payload[0].runtime;\nmsg.payload = Number(msg.payload);\nmsg.topic='runtime';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":700,"y":300,"wires":[["432295a9.4b8f1c","a799de6e.8fd11"]]},{"id":"601fe5cb.24124c","type":"function","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"","func":"msg.payload = msg.payload[0].avalosstime;\nmsg.payload = Number(msg.payload);\nmsg.topic='avalosstime';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":700,"y":340,"wires":[["b6b56e3c.13f4c","a799de6e.8fd11"]]},{"id":"a799de6e.8fd11","type":"join","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":850,"y":320,"wires":[["54cafb53.6f3a44","592118bc.a2f798"]]},{"id":"592118bc.a2f798","type":"function","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"","func":"msg.payload = ((msg.payload.runtime - msg.payload.avalosstime)/msg.payload.runtime)*100;\nmsg.payload=(msg.payload).toFixed(0);\nmsg.topic='availability';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1000,"y":320,"wires":[["44ec235d.acc4fc","dfe7a836.499c28"]]},{"id":"667f118b.bf02f","type":"comment","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"Availability CN001","info":"","x":350,"y":260,"wires":[]},{"id":"dfe7a836.499c28","type":"link out","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"sql A output CN001","links":["cc29ba1e.dff0d8","1ed3423d.8ddb1e","2cbdde5.80a4b22","92e30c3.40a9df","7afe71ee.825d4"],"x":1115,"y":320,"wires":[]},{"id":"31d9dfdc.7720d","type":"link in","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"sql A input CN001","links":["8e80bcc7.0ccfc"],"x":275,"y":320,"wires":[["3ef514cb.fef16c","2355426d.56cbee"]]},{"id":"898d2a6f.9fb7b8","type":"MSSQL-CN","tdsVersion":"7_3_B","name":"MT02","server":"192.168.21.169\\SCADASQL","port":"1433","encyption":false,"database":"MT02","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5"}]

```

👍

---

<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:** [21 June 2021 17:57 UTC](https://discourse.nodered.org/t/mssql-query-sum-value-display-in-ui-text/44432/5 "2021-06-21T17:57:32Z")

</div>

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