find number of rows without looping and with single select statement.

[Former Member]

  hi gurus,

    Need help to find the no of rows in single select statement.
    How can i modify the below sql to get count of rows of every field1 in table2?
   
    select table1~field1
           table1~field2
           from table1
           inner join table2
           on table1~field1 = table2~field2
           into corresponding fields of table alv_table
           where....
    group by table1~field1 table1~field2.

[Former Member]

I have tried this but it is dumping

   SELECT tcj04~vernr
          tcj04~verna
          count( * ) as COUNTER
     FROM tcj04
     INNER JOIN prps
     on tcj04~vernr = prps~vernr
     INTO CORRESPONDING FIELDS OF TABLE alv_grid_table
   WHERE tcj04~vernr <> '00000000'
  GROUP BY tcj04~vernr tcj04~verna.

[laurent_fournier2]

Try This :

*      SELECT SUM( LFIMG )
*             COUNT( DISTINCT MATNR ) INTO (PACKS-TOTAL,
*                                MATNR_PER_BOX)
*                          FROM PACKS
*                        WHERE PACK = PACKS-PACK.

regards.

[former_member576008]

Hi,

You can try this.

select count(a~field1)

into ( cnt )

from a as a inner join b as b

on a~field1 =b~field1

inner join c as c

on b~field1 = c~field1

inner join d as d

on c~field1 = d~field1.

where <any other clauses>.

Regards,

Nandha

[former_member214709]

Dear ,

You can opt for SELECT  statement and count the no. of rows in the TABLE using.

DATA: i(10) type c.

DESCRIBE TABLE ITAB LINES i.

Regards

Dinesh