Home / Questions / 423076
Accepted
carallot
Asked: 2022-01-25 07:29:55 +0800 CST 2022-01-25 07:29:55 +0800 CST

Group result of queries joined by UNION ALL

  • 772

Good afternoon, can you give me a hand please? I need to generate a list that returns me, given a range of dates or order numbers, a list of the items and total units that are in these orders: I achieve this with this query:

select DE.ARTICULO, DE.DEFINICION,SUM(UNIDADES)
from detalle_pedido as DE INNER JOIN cabecera_pedido as CA
ON DE.NUMERO = CA.NUMERO AND DE.LETRA = CA.LETRA
WHERE DE.NUMERO='  20000014' OR DE.NUMERO= '  20000023' 
GROUP BY DE.ARTICULO,DE.DEFINICION 
HAVING SUM(UNIDADES)>0

The problem is that the ERP where I launch this query separates the exercises by database, so this query will only work for a specific exercise. It gives me that the solution would be with a UNION ALL, and join the result to the same query in another exercise, but in this way the articles that are in orders of different exercises will be duplicated. How could I regroup and add the units of the result of the UNION ALL? Thank you very much.

sql-server

1 Answers

  1. Best Answer

    I don't think a UNION ALLis the most efficient way to do it, although it is entirely doable. In this case, UNIONyou must do it from the data itself, without aggregations, and the aggregation is done from the total number of unions.

    In this example I have put the union of two queries in two different databases (DB1 and BD2)

    SELECT ARTICULO, DEFINICION, SUM(UNIDADES)
FROM (
   select DE.ARTICULO, DE.DEFINICION, UNIDADES
   from BD1.dbo.detalle_pedido as DE 
   INNER JOIN BD1.dbo.cabecera_pedido as CA
   ON DE.NUMERO = CA.NUMERO AND DE.LETRA = CA.LETRA
   WHERE DE.NUMERO='  20000014' OR DE.NUMERO= '  20000023' 
   UNION ALL
   select DE.ARTICULO, DE.DEFINICION, UNIDADES
   from BD2.dbo.detalle_pedido as DE 
   INNER JOIN BD2.dbo.cabecera_pedido as CA
   ON DE.NUMERO = CA.NUMERO AND DE.LETRA = CA.LETRA
   WHERE DE.NUMERO='  20000014' OR DE.NUMERO= '  20000023'
   ) a
GROUP BY ARTICULO, DEFINICION
HAVING SUM(UNIDADES) > 0
    • 3