cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

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?

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

How about

CREATE VIEW myview as

     SELECT id, count(*) as "COUNT"

     FROM mytable

     GROUP BY id;

?

- Lars

Former Member
0 Kudos

Is there a way to do this without sql?

lbreddemann
Active Contributor
0 Kudos

Sure, plenty actually:

  • copy&paste and use the aggregation feature in Excel
  • ask a colleague or require an intern to manually to it for you (baaaad!)
  • stare at the numbers and count them
  • ...

Seriously: in case you want a proper answer, you may consider asking in a proper way and present the context and conditions for your requirements.

Nobody here has a crystal ball, so you need to tell us, what you want to know.

- Lars

Former Member
0 Kudos

funny. im actually an intern.

I need to do that in SAP HANA using attribute/analytical/calculation view, whichever one works.

lbreddemann
Active Contributor
0 Kudos

Practically you can do that with each of the view types.

Just check the developer guide for the examples...

However, in nearly every case yo would specify the group by and aggregation condidition in the consuming SELECT statement.

- Lars

Former Member
0 Kudos

So no matter what I have to use SQL? this guy who is tutoring me wants me to figure out how to do it without SQL 😕

0 Kudos

You can just create a calculation view and create a calculated dimension that's a counter, you are not obligated to use SQL

Former Member
0 Kudos

That did not work for me, my COUNT column returns all 1 😕

0 Kudos

Can you show us a screenshot of your view and a preview of your table data?

The solution I gave you works perfectly.

Former Member
0 Kudos

This is my table ^ i want to count the occurrence of each unique event_ID

0 Kudos

Sorry, my mistake. I was thinking about counting distinct events.

I created a table with your example values:

You should do a projection with a calculated column = 1:

And then add this calculated column as an aggregated measure on the aggregation node:

Result:

Cheers,

Fernando

Former Member
0 Kudos

Thank you very much! very helpful.

can you explain whats "new counter" used for?

0 Kudos

New counter is the equivalent of a "select count distinct", it will give you a count of all the different EVENT_IDs.

Cheers,

Fernando

Former Member
0 Kudos

How come I got a column of all 1? instead of a count of the unique IDs

0 Kudos

You have a count of 1 for each of the IDs on the EVENT_ID column. That's correct, each different ID is counted only once.

If you remove the column EVENT_ID, you will just have the total count of distinct event ids.

lbreddemann
Active Contributor
0 Kudos

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

Answers (0)