Not quite sure how COUNT() works?

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… :slight_smile:

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”)
))))

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.

2 Likes

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…?

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(),

2 Likes

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.

4 Likes

Good catch!

Hi @Steve,

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

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”
)
)
)
)
)

1 Like

Hi @Austin_Lambeth,

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

image

1 Like

@Steve

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

1 Like

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

3 Likes
Thank
	You
		For
			That...

					Very
						Cool...!!!
3 Likes