# Custom Count Query

**URL:** <https://forum.servoy.com/t/custom-count-query/19726>\
**Category:** Classic Servoy\
**Created:** [June 15, 2018, 1:25pm UTC](https://forum.servoy.com/t/custom-count-query/19726 "2018-06-15T13:25:42Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![shivdevpanchal8](https://avatars.discourse-cdn.com/v4/letter/s/a3d4f5/32.png) [@shivdevpanchal8](https://forum.servoy.com/u/shivdevpanchal8)\
**Post date:** [June 15, 2018, 1:25pm UTC](https://forum.servoy.com/t/custom-count-query/19726/1 "2018-06-15T13:25:42Z")

</div>

Trying to implement counter function for displaying number of records in custom component’s ServerJS file  
following Code is prepared:-

- @return {QBResult}  
\*/

var query = parentFoundset.getQuery();  
parentFoundset.loadRecords(query);  
console.log(query);  
/\*\* @type {QBResult} \*/  
query.result.addPk().add(query.columns.article\_id.count);  
query.groupBy.addPk().add(query.columns.article\_id);  
// query.columns.articlestatus\_id.count;

but returning Error:-  
ERROR com.servoy.j2db.util.Debug - select top 201 article\_id, article\_id, count(article\_id) from article group by article\_id , article\_id order by article\_id asc parameters:   
ERROR org.sablo.websocket.WebsocketEndpoint - Error: Wrapped com.servoy.j2db.dataprocessing.DataException: Unknown errorCode 100  
Ambiguous column name ‘article\_id’. (C:\Users\shivdev.panchal\servoy\_workspace\components1\gt\gt\_server.js#38)

Any suggestion on writing a proper query

---

<div class="post-metadata">

**Author:** ![swingman](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/swingman/32/4536_2.png) [@swingman](https://forum.servoy.com/u/swingman)\
**Post date:** [June 17, 2018, 7:29pm UTC](https://forum.servoy.com/t/custom-count-query/19726/2 "2018-06-17T19:29:12Z")

</div>

What about

```auto
var count = databaseManager.getFoundSetCount(foundset);

```

?

Beware that this has a cost (slow down your database if called frequently) for very large foundsets.

---

<div class="post-metadata">

**Author:** ![shivdevpanchal8](https://avatars.discourse-cdn.com/v4/letter/s/a3d4f5/32.png) [@shivdevpanchal8](https://forum.servoy.com/u/shivdevpanchal8)\
**Post date:** [June 18, 2018, 6:38am UTC](https://forum.servoy.com/t/custom-count-query/19726/3 "2018-06-18T06:38:44Z")

</div>

the Following Code:- var count = databaseManager.getFoundSetCount(foundset); is supported in client side which is already providing me the record count.  
But it is not valid in custom component’s server.JS file giving an error “databaseManager/ datasources” is not defined.

for loading reords:- foundset.getQuery() is used in custom component’s server.js file

Query example which is used in server.js file:-

var query = datasources.db.example\_data.person.createSelect();  
query.where.add(query.joins.person\_to\_parent.joins.person\_to\_parent.columns.name.eq(‘john’))  
foundset.loadRecords(query)

var query = datasources.db.example\_data.orders.createSelect();  
query.groupBy.addPk() // have to group by on pk when using having-conditions in (foundset) pk queries  
.root.having.add(query.joins.orders\_to\_order\_details.columns.quantity.count.eq(0))  
foundset.loadRecords(query)

both the above queries display error of datamanager / datasources in not defined.

Current Example

/\*\*  
\*@type {QBSelectdb:/vrms\_test/article}

- @return {QBResult}  
\*/  
var query1 = parentFoundset.getQuery();  
query1.result.add(query1.columns.articlestatus\_id.count);  
query1.groupBy.add(query1.columns.articlestatus\_id);  
console.log(‘aticle status id count:’ + parentFoundset.loadRecords(query1));

Error  
ERROR com.servoy.j2db.util.Debug - select top 201 article\_id, count(articlestatus\_id) from article group by articlestatus\_id order by article\_id asc parameters:   
ERROR org.sablo.websocket.WebsocketEndpoint - Error: Wrapped com.servoy.j2db.dataprocessing.DataException: Unknown errorCode 100  
Column ‘article.article\_id’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

---

<div class="post-metadata">

**Author:** ![mboegem](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/mboegem/32/4399_2.png) [@mboegem](https://forum.servoy.com/u/mboegem)\
**Post date:** [June 18, 2018, 12:56pm UTC](https://forum.servoy.com/t/custom-count-query/19726/4 "2018-06-18T12:56:13Z")

</div>

Although still bit unsure what you’re trying to do here and why,  
I do see that the count you’re trying to get in the ‘Current Example’ is never going to work.

This query, is not correct:

```auto
select top 201 article_id, count(articlestatus_id) from article group by articlestatus_id order by article_id asc

```

If you copy it, execute it in a query tool you will get the same error. So this isn’t even Servoy failing.

Because the parent foundset query you trying to use, is this:

```auto
select top 201 article_id from article order by article_id asc

```

extending it with the aggregate is not going to work.

I guess if you want a count on this selection, you should use the parent query within a new query, for example:

```auto
select articlestatus_id, count(article_id) from article group by articlestatus_id where article_id in (select top 201 article_id from article order by article_id asc)

```

Hope this helps to get this working.

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [June 18, 2018, 3:38pm UTC](https://forum.servoy.com/t/custom-count-query/19726/5 "2018-06-18T15:38:20Z")

</div>

Hi,

When I look at your original query it looks like you try to fetch the data AND get the record count back in 1 single query.  
You can do this with a Window Function like so:

```auto
SELECT TOP 201 article_id
    , COUNT(1) OVER (PARTITION BY 1) AS recordcount
FROM article 
ORDER BY article_id ASC

```

This will show the full record count in each row, even though you are just fetching 201 of them.

Hope this helps.

---

<div class="post-metadata">

**Author:** ![shivdevpanchal8](https://avatars.discourse-cdn.com/v4/letter/s/a3d4f5/32.png) [@shivdevpanchal8](https://forum.servoy.com/u/shivdevpanchal8)\
**Post date:** [June 21, 2018, 6:24am UTC](https://forum.servoy.com/t/custom-count-query/19726/6 "2018-06-21T06:24:36Z")

</div>

requesting assistance for writing query mentioned below in servoy’s custom component’s server JS file

SELECT COUNT(article\_id)  
FROM article  
where articlestatus\_id = articlestatus\_id  
group by articlestatus\_id  
ORDER BY articlestatus\_id ASC

Note:- article\_id is the primary key in the table

---

<div class="post-metadata">

**Author:** ![shivdevpanchal8](https://avatars.discourse-cdn.com/v4/letter/s/a3d4f5/32.png) [@shivdevpanchal8](https://forum.servoy.com/u/shivdevpanchal8)\
**Post date:** [June 25, 2018, 11:12am UTC](https://forum.servoy.com/t/custom-count-query/19726/7 "2018-06-25T11:12:01Z")

</div>

Code:-  
var query = parentFoundset.getQuery();  
query.result.addPk();  
var pkColumns = query.result.getColumns();  
console.log(query);  
query.result.clear();

for (var pkIndex = 0; pkIndex \< pkColumns.length; pkIndex++) {  
query.result.clear();  
query.result.add(pkColumns[pkIndex].max);  
query.result.add(query.columns.article\_code.count, ‘maximum\_items’)  
query.groupBy.add(groupColumn);  
query.sort.clear();  
}

Query Genereated:- select top 201 max(article\_id), count(article\_code) as maximum\_items from article group by articlestatus\_id order by articlestatus\_id asc

Error:- ERROR org.sablo.websocket.WebsocketEndpoint - Error: Wrapped java.lang.IllegalArgumentException: The query does not have the correct number of pks in the select