Select customer cities in the bank and count how many customers there are in each city

Question

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

I wonder how do I know if the name I’m picking from the bank already exists in the table.

at the moment I’m trying to assemble the table with the names of the 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.

Answer 1

Renan,

If you have a table with cities and one with customers 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