CODEWITHSUNDEEP
mysqlsqlsumcase-when
Kreiven
Kreiven

Reputation: 43

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

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

Upvotes: 0

Views: 130

Answers (1)

Barmar
Barmar

Reputation: 780798

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

Upvotes: 2

Related Questions