Count occurrences of role across users?

Question

The table I use stores different system names, corresponding users and their role. My target is to use the SELECT and COUNT commands together to get some output like this:

SysName | Role1 | Role2 | Role3
----------------------------------
sys1    | 10    | 5     | 25
sys2    | 0     | 70    | 12

But it seems that some of the SQL commands or their structure doesn´t work in ABAP code.

Could you let me know if something like this is possible and when how?

Answer 1

The error was triggered because the key word "DISTINCT" was missing

The correct answer should be something like:

SELECT COUNT( DISTINCT col1 ) ...

Answer 2

Select col1, COUNT( col2 ) INTO TABLE @tbl GROUP BY col1 doesn't work in abap, only Select col1, COUNT( DISTINCT col2 ) INTO TABLE @tbl GROUP BY col1. I think You should use Select + Loop in this case

Answer 3

With this Command you are able to count everything in a Table. Just add your WHERE conditions:

SELECT COUNT( * ) INTO integer FROM table [WHERE...].

I hope this is what you are looking for.