Not quite sure how COUNT() works?

[Jake_Naude]

Hi,

I don’t quite appreciate how COUNT() works?

I thought the expression below would yield 2 since there are only 2 rows that match the criteria.

Instead, I get 27… though that result is only in the two expected rows and the other rows are 0 as I would expect…?

Is there something I can read to help me understand this table/row/count concept?

Cheers…

COUNT(> SELECT(> survey[survey_branch_number],> AND(> ([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),> ([_THISROW].[LCS] = [lcs]),> OR(> ([_THISROW].[select_asset_type] = “Cabinet”),> ([_THISROW].[select_asset_type] = “Distribution Board”),> ([_THISROW].[select_asset_type] = “FOSE”),> ([_THISROW].[select_asset_type] = “HPR”),> ([_THISROW].[select_asset_type] = “LPR”)> ))))

[Austin]

Try your select statement without the count() to see what it is returning. It is likely something to do with how the select statement is being filtered not quite matching up.

[Jake_Naude]

Austin_Lambeth:

Try your select statement without the count() to see what it is returning.

Hi @Austin_Lambeth,

Thanks… I hadn’t thought of trying that…

SELECT(> survey[survey_branch_number],> AND(> [_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey],> [_THISROW].[LCS] = [lcs],> OR(> [_THISROW].[select_asset_type] = “Cabinet”,> [_THISROW].[select_asset_type] = “Distribution Board”,> [_THISROW].[select_asset_type] = “FOSE”,> [_THISROW].[select_asset_type] = “HPR”,> [_THISROW].[select_asset_type] = “LPR”> )))

Here’s why I’m getting 27… I’m not sure if it’s the total rows = 27 or the sum of the 25 items it found = 27?

Should I be writing the expression differently to only return a “1” for each row that it finds that match the criteria…?

[Steve]

Try this:

SELECT(
  survey[_ROWNUMBER],
  AND(
    [_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey],
    [_THISROW].[LCS] = [lcs],
    IN(
      [_THISROW].[select_asset_type],
      LIST(
        “Cabinet”,
        “Distribution Board”,
        “FOSE”,
        “HPR”,
        “LPR”
      )
    )
  )
)

Then wrap it in COUNT(),

[Austin]

I think it’s your OR() statement. _THISROW=“XXX” will always return true since its based on the current row not the row that is in the select statement.

[Steve]

Good catch!

[Jake_Naude]

Steve:

Try this:> …> Then wrap it in COUNT() ,

Hi @Steve,

Thanks for that… Using IN() and LIST() instead of OR() is a new trick for me… Cheers…

COUNT(> SELECT(> survey[_ROWNUMBER],> AND(> [_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey],> [_THISROW].[LCS] = [lcs],> IN(> [select_asset_type],> LIST(> “Cabinet”,> “Distribution Board”,> “FOSE”,> “HPR”,> “LPR”> )> )> )> )> )

[Jake_Naude]

Austin_Lambeth:

_THISROW=“XXX” will always return true since its based on the current row not the row that is in the select statement.

Hi @Austin_Lambeth,

Thank you… That makes sense now… And works… Cheers…

[Jake_Naude]

Steve:

Try this:

@Steve

Please share the magic trick you use to present your expressions so nicely formatted in your posts…?

[Steve]

Precede the formatted text with a line consisting only of three consecutive backticks (```), then follow it with an identical line.

[Jake_Naude]

Thank
	You
		For
			That...

					Very
						Cool...!!!