COUNT_q

Updated 2023-10-20 22:02:29.597000

Syntax

SELECT [westclintech].[wct].[COUNT_q] (
   <@RangeQuery, nvarchar(4000),>)

Description

Use the scalar function COUNT_q to count the number of rows in a resultant table that contain numbers using dynamic SQL.

Arguments

@RangeQuery

the select statement, as text, used to determine the values to be used in the COUNT_q calculation.

Return Type

float

Remarks

COUNT_q returns the number of rows in the resultant table than can be evaluated as numbers. Nulls, text, and any value that cannot be implicitly converted to float are excluded.

No GROUP BY is required for this function even though it produces aggregated results.

Examples

CREATE TABLE #c1

(

    [num] [varchar](10) NULL

);

insert into #c1

values

(1.5);

insert into #c1

values

(2.5);

insert into #c1

values

(2.75);

insert into #c1

values

(3.2);

insert into #c1

values

(5.675);

insert into #c1

values

('wct');

select wct.count_q('SELECT num from #c1');

This produces the following result

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"5"}]}