CODEWITHSUNDEEP
sqlsql-server
Christian Hernandez
Christian Hernandez

Reputation: 13

Sum the results in a union all

I have the following query in SQL.

SELECT cast(A.CodigoArticulo as varchar) CodArticulo
  , cast(ap.Codigo as varchar ) CodArtProveedor
  , dlp.Precio Publico
  , 0 Interior 
from Articulos a 
join ArticuloProveedores ap on ap.ArticuloId = a.Id 
join DetallesListaPrecios DLP on DLP.ArticuloId = A.Id 
join ListasPrecios lp on lp.Id = dlp.ListaPreciosId
where lp.Id in ('1') 
union all
SELECT cast(A.CodigoArticulo as varchar) CodArticulo
  , cast(ap.Codigo as varchar ) CodArtProveedor
  , 0 
  , dlp.Precio  
FROM Articulos a
join ArticuloProveedores ap on ap.ArticuloId = a.Id
join DetallesListaPrecios DLP on DLP.ArticuloId = A.Id 
join ListasPrecios lp on lp.Id = dlp.ListaPreciosId
where lp.Id in ('4')

And the results I get are something like below:

CodArticulo CodArtProveedor Publico     Interior

44380       K-7             697         0 
44380       K-7             0           767
00003       IM2757          0           2030
00003       IM2757          1845        0
00006       MTRJ6           156         0
00006       MTRJ6           0           172
00010       BERJ6           156         0
00010       BERJ6           0           172

I need to SUM () the columns PUBLICO and INTERIOR. And I my desired output is something like below.

CodArticulo CodArtProveedor Publico     Interior

44380       K-7             697         767
00003       IM2757          1845        2030
00006       MTRJ6           156         172
00010       BERJ6           156         172

What are the suggested methods to get my output as expected?

Upvotes: 0

Views: 3044

Answers (5)

Vaso Miruashvili
Vaso Miruashvili

Reputation: 109

This is your query into #Temp

SELECT cast(A.CodigoArticulo as varchar) CodArticulo
      , cast(ap.Codigo as varchar ) CodArtProveedor
      , dlp.Precio Publico
      , 0 Interior 
    INTO #TEMP  
    from Articulos a 
    join ArticuloProveedores ap on ap.ArticuloId = a.Id 
    join DetallesListaPrecios DLP on DLP.ArticuloId = A.Id 
    join ListasPrecios lp on lp.Id = dlp.ListaPreciosId
    where lp.Id in ('1') 
    union all
    SELECT cast(A.CodigoArticulo as varchar) CodArticulo
      , cast(ap.Codigo as varchar ) CodArtProveedor
      , 0 
      , dlp.Precio  
    FROM Articulos a
    join ArticuloProveedores ap on ap.ArticuloId = a.Id
    join DetallesListaPrecios DLP on DLP.ArticuloId = A.Id 
    join ListasPrecios lp on lp.Id = dlp.ListaPreciosId
    where lp.Id in ('4')

This is you have to add, that's all.

 select sum(Publico),sum(Interior), CodArticulo, CodArtProveedor
 from #TEMP 
 group by CodArticulo, CodArtProveedor

Upvotes: 0

Mano
Mano

Reputation: 788

I would suggest the CTE

;WITH cte(CodArticulo,CodArtProveedor,Publico,Interior) AS
(SELECT cast(A.CodigoArticulo as varchar) CodArticulo
  , cast(ap.Codigo as varchar ) CodArtProveedor
  , dlp.Precio Publico
  , 0 Interior 
from Articulos a 
join ArticuloProveedores ap on ap.ArticuloId = a.Id 
join DetallesListaPrecios DLP on DLP.ArticuloId = A.Id 
join ListasPrecios lp on lp.Id = dlp.ListaPreciosId
where lp.Id in ('1') 
union all
SELECT cast(A.CodigoArticulo as varchar) CodArticulo
  , cast(ap.Codigo as varchar ) CodArtProveedor
  , 0 
  , dlp.Precio  
FROM Articulos a
join ArticuloProveedores ap on ap.ArticuloId = a.Id
join DetallesListaPrecios DLP on DLP.ArticuloId = A.Id 
join ListasPrecios lp on lp.Id = dlp.ListaPreciosId
where lp.Id in ('4'))
select CodArticulo,CodArtProveedor,SUM(Publico),Sum(Interior) from cte group by CodArticulo,CodArtProveedor

Upvotes: 1

Mathew Paxinos
Mathew Paxinos

Reputation: 964

I'm not completely familiar with your data model, however based on the query, placing a condition inside the SUM may allow you to do the query in a single pass. In the below example a single select (no union) is executed, and a CASE statement is being used inside the SUM to get the desired result:

SELECT   cast(A.CodigoArticulo as varchar)  CodArticulo
        ,cast(ap.Codigo as varchar )        CodArtProveedor
        ,SUM(CASE WHEN '1' THEN dlp.Precio ELSE 0 END)                      Publico
        ,SUM(CASE WHEN '4' THEN dlp.Precio ELSE 0 END)                      Interior 
FROM    Articulos a 
            JOIN ArticuloProveedores ap     ON ap.ArticuloId    = a.Id 
            JOIN DetallesListaPrecios DLP   ON DLP.ArticuloId   = A.Id 
            JOIN ListasPrecios lp           ON lp.Id            = dlp.ListaPreciosId
WHERE lp.Id in ('1', '4') 
GROUP BY A.CodigoArticulo, ap.Codigo

Upvotes: 1

Mathew Paxinos
Mathew Paxinos

Reputation: 964

First of 2 possible answers:

Place your UNION query into a Common Table Expression or Sub-Query, then put the group by condition around the outside. Example Below:

SELECT   CodArticulo
        ,CodArtProveedor
        ,SUM(Publico)           as TotalPublico
        ,SUM(Interior)          as TotalInterior
FROM    (
            SELECT   cast(A.CodigoArticulo as varchar)  CodArticulo
                    ,cast(ap.Codigo as varchar )        CodArtProveedor
                    ,dlp.Precio                         Publico
                    ,0                                  Interior 
            FROM    Articulos a 
                        JOIN ArticuloProveedores ap     ON ap.ArticuloId    = a.Id 
                        JOIN DetallesListaPrecios DLP   ON DLP.ArticuloId   = A.Id 
                        JOIN ListasPrecios lp           ON lp.Id            = dlp.ListaPreciosId
            WHERE lp.Id in ('1') 

            UNION ALL

            SELECT   cast(A.CodigoArticulo as varchar)  CodArticulo
                    ,cast(ap.Codigo as varchar )        CodArtProveedor
                    ,0 
                    ,dlp.Precio  
            FROM    Articulos a
                    JOIN ArticuloProveedores ap         ON ap.ArticuloId    = a.Id
                    JOIN DetallesListaPrecios DLP       ON DLP.ArticuloId   = A.Id 
                    JOIN ListasPrecios lp               ON lp.Id            = dlp.ListaPreciosId
            WHERE lp.Id in ('4')
        ) d
GROUP BY CodArticulo, CodArtProveedor

Upvotes: 0

Rasanjana N
Rasanjana N

Reputation: 1400

I would suggest to input these UNION results to a temp table ##tempResults and then use Group By and Sum

select CodArticulo, CodArtProveedor,SUM(Publico) ,SUM(Interior) from ##tempResults
group by CodArticulo,CodArtProveedor

Upvotes: 1

Related Questions