Subscribe to this thread
Home - General / All posts - Having issues with the 'Count' aggregate function...
hsamuel111 post(s)
#11-Jun-18 19:01

I am running the following query:

Count([work_order])FROM [Data Source]::[Export_Output_20180608];

And it's not working, returns the following error:

Count([work_order])FROM [Data Source]::[Export_Output_20180608];

Invalid object reference.

I have tried using the Count function with numerous syntaxes and none of them return anything (error). What is the proper syntax for Count in Manifold because it seems different from most SQL applications I am used to

tjhb

8,168 post(s)
#11-Jun-18 23:47

I think this is meant to be a SELECT query--but there is no SELECT!

Manifold 9 diverges only slightly from standard SQL syntax for COUNT.

If the query is fixed by adding the SELECT keyword, then it will count all rows in the table for which [work_order] is not NULL.

That may or may not be what you want. For example, if all the values in the [work_order] field happened to be all the same (excluding NULLs), the count would be the raw number of values, not 1. To count the number of DISTINCT values, Manifold 9 needs a subquery. The standard COUNT(DISTINCT...) is not available in 9.

hsamuel111 post(s)
#12-Jun-18 15:19

Thank you so much! And how would I get a subquery?

tjhb

8,168 post(s)
#12-Jun-18 22:29

Wrap the first query in ( ) as the source table for the second query, like this.

--SQL9

SELECT

    COUNT([work_order]AS [n_orders]

FROM 

    (

    SELECT DISTINCT [work_order]

    FROM [Data Source]::[Export_Output_20180608]

    )

;

Remember that the first query means the inner query (subquery, nested query). It appears after the second (outer) query on the page. SQL is an onion, with the result (the outside world) at the top, the beginning in the middle.

(If there are more than two layers, as there often are, then we need words like "innermost", "outermost", "next query out", etc. Sometimes that becomes a bit tricky in English--but it is always easy to see in the code, provided attention is paid to indentation and other formatting.)

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.