Skip to Content
avatar image
Former Member

How to create a view with a column of counts of the occurence of values

If my table is:

ID

1

2

3

3

5

5

5

I want to create a view with the following result:

ID   COUNT

1     1

2     1

3     2

5     3

How would I accomplish this?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Aug 29, 2014 at 12:46 AM

    How about

    CREATE VIEW myview as

         SELECT id, count(*) as "COUNT"

         FROM mytable

         GROUP BY id;

    ?

    - Lars

    Add comment
    10|10000 characters needed characters exceeded

    • Well I would say this is a trick question from your tutor 😉

      Of course you can setup a view and rely on the default aggregation node.

      But this is technically no differnt from the SQL clause usage and basically just hides the aggregation/grouping from the SQL layer.

      Actually I am not really sure why one would ask the question like this...

      Anyhow, if that is what your tutor wanted, then you got a proper answer.

      Another option wouldbe to write a stored procedure with CE functions and table output parameter.

      - Lars