SELECT COUNT DISTINCT with 2 and more columns

[Former Member]

Hi experts, i'm trying to get count of unique combination 2 or more colums from a table. For example from table sflight i would like to get count of unique  combination of columns (CARRID CONNID). Is there any way? i have tried something like

data count_comb type i.

SELECT COUNT( DISTINCT carrid connid) INTO i FROM sflight.

but it hasnt been compiled.

Thanks for advice

Moderator message : Not enough re-search before posting, discussion locked.

Message was edited by: Vinod Kumar

[Pawan_Kesari]

With COUNT DISTINCT you can specify only one column.

You can use system variable SY-DBCNT, which gives you number of records processed be query, if you select DISTINCT data in internal table.

DATA : i_flights TYPE  TABLE OF sflight .
SELECT  DISTINCT carrid connid  INTO TABLE i_flights FROM sflight.
WRITE sy-dbcnt .

Regards,

Pawan.

[Former Member]

Yes Jorgen, the syntax is correct.

DATA: field TYPE i.

SELECT COUNT(DISTINCT value) INTO field FROM table.

This may cause a performance issue.

You can select all records and delete duplicate entries and use DESCRIBE to find the no. of records.

[former_member214878]

Dear Jorgen,

Pawan is abs. correct, there is no way you can use the Count and Distinct with more than one variable.

Please read the syntax and significance of the Agreegate Functions in SAP.

F1 on "SELECT COUNT" is the greatest one .... same as below.

http://help.sap.com/abapdocu_70/en/ABAPSELECT_AGGREGATE.htm

Appropriate PDF,  documented with example

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/20e98053-57d4-2d10-62ae-b99306324...

And the master of all..... you will get so many more info here about the agreegate functions and the comments from masters .....

http://scn.sap.com/thread/1465459

Cheers

Regards,

Ravindra Sonar

[Dhivya]

Hi Jorgen,

The following code will give count of unique  combination of columns

DATA :  no TYPE i .

SELECT DISTINCT connid carrid INTO CORRESPONDING FIELDS OF TABLE it_spfli FROM spfli .

  DESCRIBE TABLE it_spfli LINES no .

Thanks,

Dhivya B