SQL using sum

Question

I have the following structure:

|  Data  |Quantidade|QTCxVer|QTCxBra|
|04.09.18|   10     |   1   |       |
|04.09.18|   30     |       |   3   |
|04.09.18|   40     |       |   4   |
|04.09.18|   50     |   5   |       |
|05.09.18|   20     |   2   |       |
|05.09.18|   10     |       |   1   |
|05.09.18|   30     |   3   |       |

I want to have the following result:

|  Data  |TotalVer|TotalBra|
|04.09.18|   60   |  70    |
|05.09.18|   50   |  10    |

I’m trying this way, but it’s making a mistake:

select
case when QTCxVer <> 0 then
  sum(Quantidade) as "TotalVer"
else
  sum(Quantidade) as "TotalBran"
end
from tb_teste
 group by  data

Answer 1

Correcting

The flaw lies in the shape that is using the CASE.

How to do:

SELECT data,
SUM(CASE WHEN QTCxVer <> 0 THEN Quantidade END) as TotalVer,
SUM(CASE WHEN QTCxBra <> 0 THEN Quantidade END) as TotalBran
FROM tb_teste
GROUP BY data

---

Useful links

DISTINCT and GROUP BY, what is the difference between the two statements?

---

See working

• Sqlfiddle - MYSQL
• Sqlfiddle - POSTGRE

Answer 2

select
    data,
    sum(quantidade * case when qtdcxver is not null and qtdcxver <> 0 then 1 else 0 end) as TotalVer,
    sum(quantidade * case when qtdcxbra is not null and qtdcxbra <> 0 then 1 else 0 end) as TotalBra
from tb_teste
group by data