SQL using sum

3

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 get the following result:

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

I'm trying this way, but it's giving error:

select
case when QTCxVer <> 0 then
  sum(Quantidade) as "TotalVer"
else
  sum(Quantidade) as "TotalBran"
end
from tb_teste
 group by  data
    
asked by anonymous 11.09.2018 / 18:50

2 answers

7

Correcting

The fault is in the form that you are using CASE .

How to:

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

#

See working

11.09.2018 / 18:54
-1
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
    
11.09.2018 / 18:57