Using Count() in BAQ - Incorrect Syntax

@tmcmullen you can also do Sub-Select SubQuery.

Then you wouldnt need to do GROUP BY on your Top-Query.

In a Nutshell:

  1. Make a Sub-Query but DO NOT Use it on the Designer
  2. On your TopLevel Query add the Sub-Query to a Calculated Field
  3. On the Sub-Query under SubQuery Criteria you can add WHERE Clause.

Example:

SubQuery1:


SubQuery2:


Result:


image

Basically in a simpler view it does

SELECT (SELECT count(*) FROM table WHERe...) as YourCalculatedColumn

It is proven that JOINs are usually faster and sometimes SQL will even convert your SUB-SELECT when it can, but if you get into a more complex scenario where you are getting tangled in Sub-Queries, GROUPS, etc… sometimes Sub-Selects work easier. FYI :slight_smile: thought Id share something.

The best time they work is when you need to get let’s say The Last Approved Revision nothing easier than a SubSelect and then Order By ApprovedDate DESC, but when not using Aggregate like count(*) then you must make sure you set your SubQuery2 to return only 1 row, 1 column.

image

7 Likes