Select checking for a condition

Question

Select checking for a condition

Navigation

#1 by (4 votes)

2

I need to do a select where a condition is checked. Ex: If the value of a given field is < 0 assigns the sum of this field the column " PerdaGerada " otherwise assign the column " LucroGerado ".

Something like shown below:

SELECT TOP 10
       TBL.PRODUTO PRODUTO,
       COUNT(TBL.ID) AS QTD,

       SE TBL.VALORGANHO < = ENTAO SOMA DE VALOR GANHO VAI PRA COLUNA "PERDAGERADA"
       SE TBL.VALORGANHO > = ENTAO SOMA DE VALOR GANHO VAI PRA COLUNA "LUCROGERADO"


FROM TABELAVENDAS TBL
WHERE TBL.DATAEMISSAO BETWEEN '20150901' and '20150930' 
GROUP BY TBL.PRODUTO
ORDER BY 1 DESC

sql sql-server

asked by anonymous 03.02.2016 / 13:19

1 answer

Building an Array with the date range problem with daylight saving time and java applications on ubuntu server 16.04

score 4 · Answer 1

To get this result you can use CASE

Example:

 SELECT TOP 10 TBL.PRODUTO , COUNT(TBL.ID) AS QTD , 
 SUM(CASE WHEN TBL.VALORGANHO <= 0 THEN TBL.VALORGANHO ELSE 0 END) AS PERDAGERADA , 
 SUM(CASE WHEN TBL.VALORGANHO >= 0 THEN TBL.VALORGANHO ELSE 0 END) AS lUCROGERADO FROM TABELAVENDAS TBL
    WHERE TBL.DATAEMISSAO BETWEEN '20150901' and '20150930' 
    GROUP BY TBL.PRODUTO
    ORDER BY 1 DESC