CODEWITHSUNDEEP
sql
Carlos Magalhaes
Carlos Magalhaes

Reputation: 3

SQL aggregate rows in one row with different name

I have a CTE that returns the following information that is a result of several joins:

    city            Value1  Value2
    Aveiro          83  1624
    Beja            6   83
    Braga           70  1751
    Bragança        8   165
    Castelo Branco      7   206
    Coimbra         32  573
    Évora           14  173
    Faro            181 3284
    Guarda          2   106
    Ilha da Graciosa    39  92
    Ilha da Madeira 520 3339
    Ilha das Flores 53  154
    Ilha de Porto Santo 46  107
    Ilha de Santa Maria 114 140
    Ilha de São Jorge   31  116
    Ilha de São Miguel  306 2582
    Ilha do Corvo       7   12
    Ilha do Faial       90  217
    Ilha do Pico        123 167
    Ilha Terceira       205 493
    Leiria          82  1167
    Lisboa          301 8833
    Portalegre      11  201
    Porto           333 5966
    Santarém        39  973
    Setúbal         90  2229
    Viana do Castelo    24  435
    Vila Real       30  473
    Viseu           40  640

Basically they return some values by city and islands and I would like to join the values of some islands in one row with the following rule:

Madeira = Ilha de Porto Santo + Ilha da Madeira

Açores = 

Ilha da Graciosa
Ilha das Flores
Ilha de Santa Maria
Ilha de São Jorge
Ilha de São Miguel
Ilha do Corvo
Ilha do Faial
Ilha do Pico
Ilha Terceira

The output should be:

city            Value1  Value2
Aveiro          83  1624
Beja            6   83
Braga           70  1751
Bragança        8   165
Castelo Branco      7   206
Coimbra         32  573
Évora           14  173
Faro            181 3284
Guarda          2   106
Madeira         566 3446
Açores          968 3973
Leiria          82  1167
Lisboa          301 8833
Portalegre      11  201
Porto           333 5966
Santarém        39  973
Setúbal         90  2229
Viana do Castelo    24  435
Vila Real       30  473
Viseu           40  640

I try several ideas based on same posts using FOR XML PATH but I was not able to reproduce the expect result .

I don't have any ID that can be used in GROUP since Island has their own ID Can anyone help me?

Thank you

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can use a case:

select (case when city in ('Ilha de Porto Santo', 'Ilha da Madeira')
             then 'Madeira'
             when city in ('Ilha da Graciosa', 'Ilha das Flores', 'Ilha de Santa Maria',
                           'Ilha de São Jorge', 'Ilha de São Miguel', 'Ilha do Corvo',
                           'Ilha do Faial', 'Ilha do Pico', 'Ilha Terceira')
             then 'Açores'
             else city
        end) as city
       sum(Value1) as value1, sum(Value2) as value2
from cte
group by (case when city in ('Ilha de Porto Santo', 'Ilha da Madeira')
               then 'Madeira'
               when city in ('Ilha da Graciosa', 'Ilha das Flores', 'Ilha de Santa Maria',
                             'Ilha de São Jorge', 'Ilha de São Miguel', 'Ilha do Corvo',
                             'Ilha do Faial', 'Ilha do Pico', 'Ilha Terceira')
               then 'Açores'
               else city
          end);

Hmmm, thinking about this, you should probably define a reference table that has the "detailed city" and the "reporting city". This will make it easier to maintain the code in the future -- the rules about what to combine will be in a table (easy to change) rather than in code (harder to change).

Upvotes: 1

Related Questions