Francesco LinkSicily Francesco LinkSicily - 7 months ago 25
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

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