Problem adding SUM () in query

Question

Problem adding SUM () in query

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

I have this Query SQL but when wanting to add a SUM to the column CANT_DISPONIBLE generates me an error, this is my Query

select EB.ARTICULO,EB.BODEGA,EB.CANT_DISPONIBLE,EB.CANT_RESERVADA
,EB.CANT_TRANSITO,EB.CANT_REMITIDA,SUM(EB.CANT_DISPONIBLE) X
from EXISTENCIA_BODEGA EB,ARTICULO CL
where 
 EB.ARTICULO=CL.ARTICULO AND
(EB.CANT_DISPONIBLE>0 or EB.CANT_REMITIDA>0 or 
 EB.CANT_TRANSITO>0 or EB.CANT_RESERVADA>0)
and (SUBSTRING(EB.BODEGA,1,1)='T'
OR SUBSTRING(EB.BODEGA,1,2)='VM'
OR SUBSTRING(EB.BODEGA,1,2)='CS')
and SUBSTRING(EB.BODEGA,1,2)<>'TA'
AND CL.CLASIFICACION_1  IN ('1010','1007')
order by EB.BODEGA

The error is because of:

Group By: Column 'EXISTENCIA_BODEGA.ARTICULO'
is invalid in the select list because it is not contained
in either an aggregate function or the GROUP BY clause.

sql sql-server

asked by Ivxn 16.05.2016 в 20:42

source

2 answers

1

As I said, you only need to add the group by clause (as indicated by the error strace) since you added the function sum in particular your query should be like this

SELECT EB.ARTICULO,
    EB.BODEGA,
    EB.CANT_DISPONIBLE,
    EB.CANT_RESERVADA ,
    EB.CANT_TRANSITO,
    EB.CANT_REMITIDA,
    SUM(EB.CANT_DISPONIBLE) X
FROM EXISTENCIA_BODEGA EB, ARTICULO CL
WHERE EB.ARTICULO            = CL.ARTICULO
AND (EB.CANT_DISPONIBLE      > 0
OR EB.CANT_REMITIDA          > 0
OR EB.CANT_TRANSITO          > 0
OR EB.CANT_RESERVADA         > 0)
AND (SUBSTRING(EB.BODEGA,1,1) = 'T'
OR SUBSTRING(EB.BODEGA,1,2)   = 'VM'
OR SUBSTRING(EB.BODEGA,1,2)   = 'CS')
AND SUBSTRING(EB.BODEGA,1,2) <> 'TA'
AND CL.CLASIFICACION_1      IN ('1010','1007')
GROUP BY EB.ARTICULO,
    EB.BODEGA,
    EB.CANT_DISPONIBLE,
    EB.CANT_RESERVADA ,
    EB.CANT_TRANSITO,
    EB.CANT_REMITIDA
ORDER BY EB.BODEGA;

answered by 16.05.2016 в 21:02

Can a new operator be created in JavaScript? [duplicate] Get distance between two declared points in a struct

score 2 · Accepted Answer

When you use an aggregate function (such as SUM) in an SQL, fields in the SELECT that are not included in aggregate functions must be specified in the GROUP BY clause.

The correction of your query depends on what you want to obtain.

For example, if you want to obtain the sums of the amounts for each item and warehouse:

    SELECT EB.ARTICULO, EB.BODEGA, 
        SUM(EB.CANT_DISPONIBLE),SUM(EB.CANT_RESERVADA), 
        SUM(EB.CANT_TRANSITO), SUM(EB.CANT_REMITIDA), 
        SUM(EB.CANT_DISPONIBLE) X
    FROM EXISTENCIA_BODEGA EB, ARTICULO CL
    WHERE 
        EB.ARTICULO=CL.ARTICULO AND
        (EB.CANT_DISPONIBLE>0 OR EB.CANT_REMITIDA>0 OR
            EB.CANT_TRANSITO>0 OR EB.CANT_RESERVADA>0)
        AND (SUBSTRING(EB.BODEGA,1,1)='T'
        OR SUBSTRING(EB.BODEGA,1,2)='VM'
        OR SUBSTRING(EB.BODEGA,1,2)='CS')
        AND SUBSTRING(EB.BODEGA,1,2)<>'TA'
        AND CL.CLASIFICACION_1  IN ('1010','1007')
    GROUP BY EB.ARTICULO, EB.BODEGA
    ORDER BY EB.BODEGA