I have this query
SELECT
t.SI_Articulo,
m.SI_Descripcion,
t.SI_UM,
CASE WHEN SI_OV IS NOT NULL THEN '*' ELSE NULL END SI_OV,
COUNT(DISTINCT t.SI_Ubicacion) SI_Ubicacion,
SUM(DISTINCT t.SI_Existencia) SI_Existencia,
SUM(DISTINCT c.SI_Cantidad) SI_Cantidad,
SUM(DISTINCT c.SI_Cantidad) - SUM(DISTINCT t.SI_Existencia) SI_DIF,
SUM(DISTINCT c.SI_Cantidad) - SUM(DISTINCT t.SI_Existencia)* m.SI_Costo_Promedio AS SI_Dif_Dinero
FROM SI_Inventario_Teorico_QAD t
INNER JOIN SI_Maestro_Ref_QAD m ON m.SI_Num_Articulo = t.SI_Articulo
INNER JOIN SI_Conteo c ON c.SI_Num_Articulo = t.SI_Articulo
GROUP BY t.SI_Articulo,
m.SI_Descripcion,
t.SI_UM,
c.SI_OV,
m.SI_Costo_Promedio;
which gives me the following result
+-------------+----------------+-------+-------+--------------+---------------+-------------+--------+---------------+
| SI_Articulo | SI_Descripcion | SI_UM | SI_OV | SI_Ubicacion | SI_Existencia | SI_Cantidad | SI_DIF | SI_Dif_Dinero |
+-------------+----------------+-------+-------+--------------+---------------+-------------+--------+---------------+
| 013058 | XXXX | ud | * | 1 | 1 | 3 | 2 | -5657 |
| 200002 | XXXX | ud | * | 5 | 73 | 7 | -66 | -15443332 |
| 200002 | XXXX | ud | * | 5 | 73 | 10 | -63 | -15443329 |
+-------------+----------------+-------+-------+--------------+---------------+-------------+--------+---------------+
The error is that the amount of the same reference is not being added 200002
, since they do not have the same ( SI_OV
), so it separates it, but if I comment this line
CASE WHEN SI_OV IS NOT NULL THEN '*' ELSE NULL END SI_OV
and I also comment on the , yes it does the sum.GROUP BY
c.SI_OV
Any way to tell it to add ignore me SI_OV
but include it as aggregation condition?
The problem is that using the column
c.SI_OV
in theGROUP BY
is giving you this result. What you have to do in this case is use the expressionCASE
in theGROUP BY
instead of the column:In any case, I recommend that you review the use of the column
m.SI_Costo_Promedio
directly in the multiplication, without going through an aggregation function (it may be fine, but at first glance it seems suspicious)