topic Re: select with count in Application Development and Automation Discussions

select with count

Former Member — Tue, 09 Jan 2007 14:23:42 GMT

Hallow I have to do a select from table pa0001 (table of employee )and I wont just to count the number of employee in this table . how can I do that .

This is my select how can I improve it (like persg).

( i heard about select count but i dont now how to use it)

Thanks for your time.

SELECT

FROM pa0001

INTO wa_teken_itab-ex_immobile

WHERE persg = '6'

AND persg = '7'

AND persg = '8'

AND orgeh = 'Z'

AND orgeh NE '00009999'

AND stell = 'A'.

regards

Re: select with count

Former Member — Tue, 09 Jan 2007 14:26:13 GMT

SELECT COUNT( * ) FROM pa0001
INTO wa_teken_itab-ex_immobile
WHERE persg = '6'
AND persg = '7'
AND persg = '8'
AND orgeh = 'Z'
AND orgeh NE '00009999'
AND stell = 'A'.

Re: select with count

Former Member — Tue, 09 Jan 2007 14:28:49 GMT

HI,

SELECT COUNT( DISTINCT EMPID )

FROM pa0001

INTO IT_EMPID_COUNT

WHERE persg = '6'

AND persg = '7'

AND persg = '8'

AND orgeh = 'Z'

AND orgeh NE '00009999'

AND stell = 'A'.

now the field IT_EMPID_COUNT will have the No of records

Regards

Sudheer

Re: select with count

Former Member — Tue, 09 Jan 2007 14:29:59 GMT

Hello Antonio,

U can use like this

SELECT <b>COUNT(*)</b>

FROM pa0001

INTO wa_teken_itab-ex_immobile

WHERE persg = '6'

AND persg = '7'

AND persg = '8'

AND orgeh = 'Z'

AND orgeh NE '00009999'

AND stell = 'A'.

If this one is not working then,

Select all the records into Itab and use.

data: lv_no type i.

DESCRIBE TABLE ITAB LINES LV_NO.

LV_NO will have he number of records.

You can also use the SY-DBCNT.,

If useful reward.

Vasanth

Re: select with count

Former Member — Tue, 09 Jan 2007 14:30:56 GMT

try this code

data : count type i.

select count( * ) into count FROM pa0001

INTO wa_teken_itab-ex_immobile

WHERE persg = '6'

AND persg = '7'

AND persg = '8'

AND orgeh = 'Z'

AND orgeh NE '00009999'

AND stell = 'A'.

Dont forget to reward points.

Regards,

barath.

Re: select with count

RichHeilman — Tue, 09 Jan 2007 14:30:58 GMT

You can't use COUNT, because there will be more than one record per employee. These records are date sensitive, so everytime there is a change, a new record is created with begin/end dates. So you will need to get all of these records within your condition, then sort by PERNR and delete the adjacent records.



report zrich_0001.

data: no_employees type i.
data: ipa0001 type table of pa0001.

select * from pa0001
             into table ipa0001
                 where persg = '6'
                 and persg = '7'
                 and persg = '8'
                 and orgeh = 'Z'
                 and orgeh ne '00009999'
                 and stell = 'A'.

sort ipa0001 by pernr ascending.
delete adjacent duplicates from ipa0001 comparing pernr.


describe table ipa0001 lines no_employees.

write:/ no_employees.

Regards,

Rich Heilman

Re: select with count

Former Member — Tue, 09 Jan 2007 14:37:44 GMT

Maybe I'm crazy, but is there ever going to be a record where persg = '6' AND '7' AND '8'?

Re: select with count

RichHeilman — Tue, 09 Jan 2007 14:40:54 GMT

Hahah. You're right Matt. There is definitly an issue with it. And the logic for ORGEH doesn't make sense either.

where ( persg = '6'
     or  persg = '7'
     or  persg = '8' )
     and orgeh = 'Z'    
     and stell = 'A'.

Regards,

Rich Heilman

Message was edited by:

Rich Heilman

Re: select with count

Former Member — Tue, 09 Jan 2007 15:32:16 GMT

Just add an additional condition to get a distinct list of current employees

and begda le sy-datum

and endda ge sy-datum.

Re: select with count

Former Member — Tue, 09 Jan 2007 15:44:41 GMT

select * from pa0001

into table itab

where persg = '6'

and persg = '7'

and persg = '8'

and orgeh = 'Z'

and orgeh ne '00009999'

and stell = 'A'.

sort itab by pernr ascending.

delete adjacent duplicates from itab comparing pernr.

describe table itab lines count.

write:/ count.

AWARD POINTS