How to use SELECT COUNT in subquery

[dschiener]

Hi there,

given the following database table structure including data.

|contract_id|personnel_number|metadata|
|ABC123     |1122            |  ...   |
|ABC124     |1122            |  ...   |
|ABC125     |1122            |  ...   |
|ABC126     |2233            |  ...   |
|ABC127     |2233            |  ...   |
|ABC128     |3344            |  ...   |

Using a SELECT with COUNT I would like to get the following result:

|contract_id|personnel_number|metadata|count|
|ABC123     |1122            |  ...   | 3   |
|ABC124     |1122            |  ...   | 3   |
|ABC125     |1122            |  ...   | 3   |
|ABC126     |2233            |  ...   | 2   |
|ABC127     |2233            |  ...   | 2   |
|ABC128     |3344            |  ...   | 1   |

So the COUNT should count the number of contract IDs related to the same personnel number.

Can anyone help me out?

SELECT
  FROM dtab AS contracts
  FIELDS
    contracts~*,
    ( SELECT COUNT(*) FROM dtab WHERE contracts~personnel_number = ?? ) AS coun

Best regards,
Daniel

[fedaros]

Hi Daniel,

Could not understand the issue you are facing. On your example the SQL alone works fine:

SELECT COUNT(*) FROM dtab WHERE contracts~personnel_number = ??

If you want for all personnel_numbers just add a GROUP BY:

SELECT COUNT(*),personnel_number FROM dtab GROUP BY personnel_number

Please explain more.

Regards, Fernando Da Rós

[RaymondGiuseppi]

Try something such as

SELECT
  FROM dtab AS contracts
  FIELDS
    contracts~*,
    ( SELECT COUNT(*) FROM dtab AS anotherid 
	  WHERE contracts~personnel_number = anotherid~personnel_number ) AS count.

[dschiener]

raymond.giuseppi, unfortunately this does not work, syntax error.

It seems I cannot use COUNT in a subquery like this :(... but thats part of my question.

[dschiener]

Hi fedaros,

yes sure, but then it is a distinct SQL query that will only provide the count as result.

In general you are right. Using COUNT I want to count the group size of personnel number.

But.

I want 1 SQL query that returns 1 internal table that provides the following structure:

|contract_id|personnel_number|metadata|count|
|ABC123     |1122            |  ...   | 3   |
|ABC124     |1122            |  ...   | 3   |
|ABC125     |1122            |  ...   | 3   |
|ABC126     |2233            |  ...   | 2   |
|ABC127     |2233            |  ...   | 2   |
|ABC128     |3344            |  ...   | 1   |

So for each row I want to return an additional field count that contains the group size of the related personnel number.

BR
Daniel

[Sandra_Rossi]

Use WITH if you have ABAP 7.51 and more (I think that you need just one WITH)

WITH +cte1 AS SELECT ... FROM ... ,
WITH +cte2 AS SELECT ... FROM ...
SELECT ... FROM +cte1 INNER JOIN +cte2 
...
INTO TABLE ...

[dschiener]

Hi sandra.rossi,
unfortunately we use ABAP 7.50 :(:(.

BR
Daniel

[Sandra_Rossi]

If you have 7.51 then use ABAP "WITH" (common table expressions)

Else if you have HANA then use AMDP to run SQLscript directly

Else either use native SQL (ADBC for instance)

or use 2 SQL queries instead of 1

Else tell us more.

[fedaros]

Hi Daniel,

Understood. Despite it be easy on SQL database, perhaps you don't have it ready to user on open SQL.

on SQL you can put a subselect on SELECT (query on result set), or join with result of counts.. but none of these works on your version.

Regards, Fernando Da Rós