Francesco LinkSicily Francesco LinkSicily - 1 year ago 80
SQL Question

SUM IIF expression result

in this select i need the sum result the IIF expression, but when i execute this query obtain only first IIF statement. Any suggestion?? Thanks

SELECT conto, desconto, date, codoperaio, SUM(IIF(totcasse ='1',SUM(totcasse),0)+
IIF(totcasse ='6',SUM(totcasse*3),0)+
IIF(totcasse ='8',SUM(totcasse*4),0)+
IIF(totcasse ='10',SUM(totcasse*5),0)) as Kilogrammi
FROM dbo.Import
where totcasse BETWEEN 1 and 10
Group by conto, desconto, date,codoperaio, totcasse

Answer Source

If am not wrong you are looking for this

SELECT conto,
       desconto,
       date,
       codoperaio,
       Sum(CASE totcasse WHEN '1' THEN totcasse ELSE 0 END) + 
       Sum(CASE totcasse WHEN '6' THEN totcasse * 3 ELSE 0 END) + 
       Sum(CASE totcasse WHEN '8' THEN totcasse * 4 ELSE 0 END) + 
       Sum(CASE totcasse WHEN '10' THEN totcasse * 5 ELSE 0 END)
FROM   dbo.Import
WHERE  totcasse in (1,6,8,10)
GROUP  BY conto,
          desconto,
          date,
          codoperaio 

or

SELECT conto,
       desconto,
       date,
       codoperaio,
       Sum(CASE totcasse
             WHEN '1' THEN totcasse 
             WHEN '6' THEN totcasse * 3
             WHEN '8' THEN totcasse * 4
             WHEN '10' THEN totcasse * 5
           END)
FROM   dbo.Import
WHERE  totcasse in (1,6,8,10)
GROUP  BY conto,
          desconto,
          date,
          codoperaio