Sum A Union Query

November 15, 2024 Post a Comment

I'm using Microsoft SQL Svr Mgmt Studio 2008. I don't have access to create a temporary table (company restricts ability to create or modify tables) or I would use that to solve t

Solution 1:

You need to alias your derived table, you must also use a group by with a having clause.

SELECT
  q1.Period, 
  q1.PCC, 
  SUM(q1.BasicHits), 
  SUM(q1.FareHits), 
  SUM(q1.SearchHits)
FROM (SELECT     
        AAAPeriod ASPeriod, 
        AAAFromPCC AS PCC, 
        -SUM(AAABasic) AS BasicHits,
        -SUM(AAAFare) AS FareHits,
        -SUM(AAASearch) AS SearchHits
      FROM HitsAaa
      GROUPBY 
        AAAPeriod, 
        AAAFromPCC
      HAVING (AAAPeriod = N'2010-10') 

      UNIONALLSELECT     
        AAAPeriod ASPeriod, 
        AAAtoPCC AS PCC,
        SUM(AAABasic) AS BasicHits,
        SUM(AAAFare) AS FareHits,
        SUM(AAASearch) AS SearchHits 
      FROM HitsAaa
      GROUPBY 
        AAAPeriod, 
        AAAtoPCC
      HAVING (AAAPeriod = N'2010-10')

      UNIONALLSELECT
        AgtPeriod ASPeriod, 
        AgtPcc AS PCC, 
        SUM(AgtBasic) AS BasicHits,
        SUM(AgtFare) AS FareHits,
        SUM(AgtSearch)  AS SearchHits
      FROM HitsAgent
      GROUPBY 
        AgtPeriod, 
        AgtPCC
      HAVING (AgtPeriod = N'2010-10')) q1 
GROUPBY 
  q1.Period, 
  q1.PCC

Solution 2:

SQL Server requires that you define a table alias for a derived table/inline view:

SELECT x.period, x.pcc, SUM(x.BasicHits), SUM(x.FareHits), SUM(x.SearchHits)
  FROM (SELECT AAAPeriod ASPeriod, 
               AAAFromPCC AS PCC, 
               -SUM(AAABasic) AS BasicHits,
               -SUM(AAAFare) AS FareHits,
               -SUM(AAASearch) AS SearchHits
          FROM HitsAaa
         WHERE AAAPeriod = N'2010-10'GROUPBY aaaperiod, aaafrompcc
        UNIONALLSELECT AAAPeriod, 
               AAAtoPCC,
               SUM(AAABasic),
               SUM(AAAFare),
               SUM(AAASearch) 
          FROM HitsAaa
         WHERE AAAPeriod = N'2010-10'GROUPBY aaaperiod, aaafrompcc
        UNIONALLSELECT AgtPeriod, 
               AgtPcc, 
               SUM(AgtBasic),
               SUM(AgtFare),
               SUM(AgtSearch)
          FROM HitsAgent
         WHERE AgtPeriod = N'2010-10'GROUPBY agtperiod, agtpcc) AS x
GROUPBY x.period, x.pcc

Solution 3:

I don't have access to create a temporary table (company restricts ability to create or modify tables) or I would use that to solve this problem.

Instead of a temporary table, try using a table variable:

declare@ttable (id intprimary key, col1 varchar(50))
insert@t (col1) values ('hello table variable')
select*from@t

A table variable can do most of the things a temporary table can.

Like Martin's (now deleted) answer suggests, consider giving the subquery an alias, like:

select  ... list of columns ...
from    (
        ... subquery ...
        ) as SubQueryAlias
groupby
        col1

And in your subquery, the having should probably be a where:

...
FROM        HitsAaa
WHERE       (AAAPeriod = N'2010-10') 
...

Solution 4:

Change your first line to

Select T.Period, T.PCC, SUM(T.BasicHits), SUM(T.FareHits), SUM(T.SearchHits)

and the last line to

) T GROUPBY T.Period, T.PCC

You need to define a table alias (in this case T) for inner tables

Also, you need to GROUP BY the inner queries

Solution 5:

select R."Artigo", R."Dscription" as "Descricao", R."Codigo", R."Cliente", R."ItmsGrpCod", Sum(R."Qtd/Kg") as "Qtd/Kg", Sum(R."Valor") as "Valor", Sum(R."Valor")/Sum(R."Qtd/Kg") as "PMVL" from ( select T1."ItemCode" as "Artigo", T1."Dscription", T0."CardCode" as "Codigo", T0."CardName" as "Cliente", T3."ItmsGrpCod", Sum(T1."Quantity")-1 as "Qtd/Kg", Sum(T1."LineTotal")-1 as "Valor", ( Sum(T1."LineTotal") / Sum(T1."Quantity") ) as "PMVL" from ORIN T0 INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry" RIGHT JOIN OITM T3 ON T1."ItemCode" = T3."ItemCode" LEFT JOIN IBT1 T2 ON T0."DocEntry" = T2."BaseEntry" AND T1."ItemCode"=T2."ItemCode" and T2."BaseLinNum"=T1."LineNum" where T0."DocDate" >= ('2021-03-19') and T0."DocDate" <= ('2021-03-19') and T3."ItmsGrpCod" like '___' and T0.CANCELED = 'N' group by T1."ItemCode", T1."Dscription", T0."CardCode", T0."CardName", T3."ItmsGrpCod" union -- facturas

select T1."ItemCode" as "Artigo", T1."Dscription", T0."CardCode" as "Codigo", T0."CardName" as "Cliente", T3."ItmsGrpCod", Sum(T1."Quantity") as "Qtd/Kg", Sum(T1."LineTotal") as "Valor", ( Sum(T1."LineTotal") / Sum(T1."Quantity") ) as "PMVL" from OINV T0 INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry" -- LEFT JOIN OITM T3 ON T1."ItemCode" = T3."CardCode" left JOIN OITM T3 ON T1."ItemCode" = T3."ItemCode" -- INNER JOIN OITM T3 ON T3."ItemCode" = T3."CardCode" LEFT JOIN IBT1 T2 ON T0."DocEntry" = T2."BaseEntry" AND T1."ItemCode"=T2."ItemCode" and T2."BaseLinNum"=t1."LineNum"

where T0."DocDate"  >= ('2021-03-19') and T0."DocDate" <= ('2021-03-19') 
    and T3."ItmsGrpCod" like '___'groupby T1."ItemCode", T1."Dscription", T0."CardCode", T0."CardName",T3."ItmsGrpCod"
    ) as R
    groupby R."Artigo", R."Dscription", R."Codigo", R."Cliente",R."ItmsGrpCod"

Lab Sql