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

help.appsheet.com

COUNT()

Number of list items

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

[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]

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...!!!

[Austin · Accepted solution]

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]

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…