How to sum values from different rows in SQL that have the same argument in CASE-WHEN

Question

Here is my code and this is my problem:

I have three diferente values for codCanal that I want to name as Rota (02, 03 and 05). But when I run this query, I get three different rows for this value. Something like:

Ano | Mes | Canal | Volume
2015 | 01 | AS | 4423
2015 | 01 | Rota | 552
2015 | 01 | Rota | 744
2015 | 01 | Rota | 1223 
2015 | 01 | HSA | 6510

I want to show just 1 row with the sum of these values (552 + 744 + 1223). How can I solve this?

I tried to use SUM(CASE WHEN) but it returns a new column, and this is not what I want.

Thanks.

SELECT
    Vendas.datAno AS Ano,
    Vendas.datMes AS Mes,

    CASE WHEN Cliente.codCanal IN ('01') THEN 'AS'
    WHEN Cliente.codCanal IN ('02','03','05') THEN 'Rota'
    WHEN Cliente.codCanal IN ('08') THEN 'HSA'
    END AS Canal,

    ROUND(SUM(Vendas.vlrVolumeLiquido),0) AS Volume

FROM
    tblDadMetaRealCliente Vendas
    INNER JOIN
    tblCadOrganizacaoVenda OV
    ON Vendas.codOrganizacaoVenda = OV.codOrganizacaoVenda
    INNER JOIN
    tblCadCliente Cliente
    ON Vendas.codCliente = Cliente.codCliente
    INNER JOIN
    categoria_simulador_nova Catsim
    ON Vendas.codMaterial = Catsim.codMaterial

WHERE
    Catsim.Catsim IN ('CHESTER LANCHE') AND
    Vendas.datAno IN ('2014', '2015') AND
    Cliente.codCanal IN ('01', '02', '03', '05', '08')

GROUP BY
    Vendas.datAno,
    Vendas.datMes,
    Cliente.codCanal

ORDER BY
    Vendas.datAno,
    Vendas.datMes,
    Cliente.codCanal

Not sure if I got what you meant. I was trying to figure it out and the only way I found (didn't test though) is to use the result table of this query and create a new one using this SUM and WHERE. Would be this the best solution? — Kreiven
– Kreiven, Commented Dec 22, 2015 at 16:25
as a subquery taking the place of one of the fields SELECT SUM(*) WHERE Cliente.codCanal IN('02,'03',05'). — Anton
– Anton, Commented Dec 22, 2015 at 16:40

Barmar · Accepted Answer · 2015-12-22 16:43:06Z

2

You need to use the converted column name in your GROUP BY clause, so that it will combine them into one group.

GROUP BY Vendas.datAno, Vendas.datMes, Canal
ORDER BY Vendas.datAno, Vendas.datMes, Canal

The above is for MySQL. In SQL-Server, you need to move the query into a subquery.

SELECT Ano, Mes, Canal, SUM(Volume) AS Volume
FROM (your query) AS subquery
GROUP BY datAno, datMes, Canal

edited Dec 22, 2015 at 16:43

answered Dec 22, 2015 at 16:07

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Kreiven Over a year ago

Actually, if I try to use just Canal it returns an error. I used Cliente.codCanal instead).

Kreiven Over a year ago

The error is: Mensagem 207, Nível 16, Estado 1, Linha 32 Invalid column name 'Canal'. It seems that he can't find the new column name. Maybe doing a new query would work? SELECT Ano, Mes, Canal, SUM(Volume) FROM [result of this query] GROUP BY Ano, Mes, Canal

Barmar Over a year ago

Are you sure you're using MySQL? That doesn't look like a MySQL error message.

Barmar Over a year ago

"Invalid column name" seems to be a SQL-Server error, not MySQL.

Barmar Over a year ago

See stackoverflow.com/questions/497241/…

|

Collectives™ on Stack Overflow

How to sum values from different rows in SQL that have the same argument in CASE-WHEN

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related