Select client cities in the bank and count how many clients there are in each city

Question

Select client cities in the bank and count how many clients there are in each city

Navigation

#1 by (0 votes)

0

I need to select the cities of the customers registered in the bank, and count how many clients there are in each city.

I would like to know how I know if the name I'm getting from the database already exists in the table.

At the moment I'm trying to mount the table with the names of cities without repeating their names.

TYPES: BEGIN OF gy_city
 , name(30) TYPE c
 , qtd      TYPE i
 , END OF gy_city.

DATA: gs_kna1 TYPE kna1
, gs_city TYPE gy_city
, gt_kna1 TYPE TABLE OF kna1
, gt_city TYPE TABLE OF gy_city.



SELECT kunnr name1 regio mcod3 
INTO CORRESPONDING FIELDS OF TABLE gt_kna1
FROM kna1 
WHERE regio eq 'PR'.

LOOP AT gt_kna1 INTO gs_kna1.
  IF gs_kna1-mcod3 IN gt_city-name.
  WRITE 'ADICIONADO'.
  gs_city-name = gs_kna1-mcod3.
  APPEND gs_city to gt_city.
ELSE.
  WRITE 'REPETIDO'.
ENDIF.

ENDLOOP.

I found this solution:

TYPES: BEGIN OF gy_city
     , name(30) TYPE c
     , qtd      TYPE i
     , END OF gy_city.

DATA: gs_kna1 TYPE kna1
    , gt_kna1 TYPE TABLE OF kna1
    , gt_city TYPE SORTED TABLE OF gy_city WITH UNIQUE KEY name
    , gs_city LIKE LINE  OF gt_city
    , gd_city_name(30) TYPE c.


* Select para table
SELECT mcod3 INTO CORRESPONDING FIELDS OF TABLE gt_kna1 FROM kna1 WHERE regio EQ 'PR'ORDER BY mcod3 ASCENDING.

LOOP AT gt_kna1 INTO gs_kna1.

  READ TABLE gt_city
    INTO gs_city
    WITH KEY name = gs_kna1-mcod3.

  IF sy-subrc EQ 0.
    gs_city-qtd = gs_city-qtd + 1.
    MODIFY gt_city FROM gs_city INDEX sy-tabix.

  ELSE.
    gs_city-name = gs_kna1-mcod3.
    gs_city-qtd  = 1.
    APPEND gs_city TO gt_city.
  ENDIF.
ENDLOOP.
*SORT gt_city BY name ASCENDING.

sap abap erp

asked by anonymous 07.06.2016 / 17:26

1 answer

Send a string in javascript Webbrowser click button without name and ID DELPHI

score 0 · Accepted Answer

Renan,

If you have a table with the cities and one with the clients and in this table you have the city id. It can be done as follows.

select c.nomeCidade, count(*) quantidadeClientes from cidades c
inner join clientes cl on c.IdCidade = cl.IdCidade
group by c.nomeCidade

The result would be something like:

nomeCidade       quantidadeClientes
São Paulo        10
Rio de Janeiro   5
Bahia            3
Acre             0