CODEWITHSUNDEEP
sqlsql-server
Joel
Joel

Reputation: 65

Filtering SQL Group on SUM column

I want to use this SQL syntax . but I am receiving this error on nSaldo column :

SELECT [d_tipo1]
     , [d_tipo]
     , [d_numero]
     , [d_dfecha]
     , sum(dvalor) as nsaldo 
     , [nit]
     , [cuenta]
     , [codcli] 
  FROM [dbcrcar] 
 WHERE (nsaldo>0)  
 GROUP BY 
       nit
     , codcli
     , cuenta 
     , d_tipo1
     , d_tipo
     , d_numero
     , d_dfecha  
 ORDER BY 
       [d_dfecha]

I want to show only registers that meet the condition nsaldo>0

Thanks

Upvotes: 1

Views: 9211

Answers (1)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186803

When filtering on aggregated values you should use HAVING, not WHERE:

  SELECT [d_tipo1], 
         [d_tipo], 
         [d_numero], 
         [d_dfecha], 
         Sum(dvalor) as nsaldo, 
         [nit], 
         [cuenta], 
         [codcli] 
    FROM [dbcrcar] 
GROUP BY nit, 
         codcli, 
         cuenta,
         d_tipo1,
         d_tipo,
         d_numero,
         d_dfecha 
  HAVING (Sum(dvalor) > 0) -- Use HAVING with aggregated values, not WHERE
ORDER BY [d_dfecha]

Upvotes: 7

Related Questions