InterSystems Caché: How to count encounters of values in list in table?

Question

I'm very new to Caché and was asked to count encounters of values in list in table. For example, table looks like this:

id | values
-------------------
1  | {1, 2}
2  | {1, 3, 4, 4, 5}
3  | {1, 2, 5}
4  | {1, 2, 2, 4, 5}
5  | {2, 1, 4}

Class Definition

Class Object.Test Extends %Persistent
{
  Property values As list of %Integer
}

And output for that table must look like this:

value | count
--------------
 1    |   5
 2    |   5
 3    |   1
 4    |   4
 5    |   3

But I have no clue how to do this

EDIT Added class definition

Answer 1

Assuming you don't have data you need to save.

1. Project list property as a separate table

   Class Object.Test Extends %Persistent { Property values As list of %Integer(STORAGEDEFAULT="array", SQLPROJECTION = "table/column"); }

2. Delete data, storage definition and recompile the class.

3. Generate the data.

4. Execute SQL query

   SELECT "values", count(*) AS "count" FROM Object.Test_values GROUP BY "values"

5. (Optional) Add and build collection indices.

Notes.

That's SQL way. You can also do it:

• object way (iterate over object and their properties)
• global way (iterate over global and this property values)

Caché commmunity forum.