Combine the output of two queries into a single query-CodePudding

I want to combine the below queries into a single output, I am not sure on how to use union as I have bunch of conditions on the queries as well. In the final output I also need to Subtract the result of negative_sales column from query 2 from Net value column from query 1

SELECT storenumber,
       bbbtendertypetext,
       SUM(financialamounttendered) - SUM(financialchangeamount) AS NET_VALUE,
       COUNT(transactionid) AS Transaction_Count
FROM   [AceTLogData].[Tlog].[tender]
WHERE  transactiondatetime > '2022-09-21 00:00:00.000'
       AND transactiondatetime < '2022-09-22 00:03:00.000'
GROUP  BY storenumber, bbbtendertypetext
ORDER  BY storenumber 

SELECT storenumber,
       bbbtendertypetext,
       SUM(financialamounttendered) AS negative_Net_Sales,
       COUNT(transactionid) AS Transaction_Count
FROM   [AceTLogData].[Tlog].[tender_correct]
       where transactiondatetime > '2022-09-21 00:00:00.000'
       AND transactiondatetime < '2022-09-22 00:03:00.000'
GROUP  BY storenumber, bbbtendertypetext
ORDER  BY storenumber

CodePudding user response：

For example so

select s1.storenumber storenumber
     , s2.storenumber storenumber2
     , s1.bbbtendertypetext bbbtendertypetext
     , s2.bbbtendertypetext bbbtendertypetext2
     , s1.NET_VALUE  NET_VALUE
     , s1.Transaction_Count Transaction_Count
     , s2.Transaction_Count Transaction_Count2
     , s2.negative_Net_Sales negative_Net_Sales
     , s1.NET_VALUE - s2.negative_Net_Sales differnt
from (
    SELECT storenumber,
           bbbtendertypetext,
           SUM(financialamounttendered) - SUM(financialchangeamount) AS NET_VALUE,
           COUNT(transactionid) AS Transaction_Count
    FROM   [AceTLogData].[Tlog].[tender]
    WHERE  transactiondatetime > '2022-09-21 00:00:00.000'
           AND transactiondatetime < '2022-09-22 00:03:00.000'
    GROUP  BY storenumber, bbbtendertypetext
    ORDER  BY storenumber 
) s1 left join (
    SELECT storenumber,
           bbbtendertypetext,
           SUM(financialamounttendered) AS negative_Net_Sales,
           COUNT(transactionid) AS Transaction_Count
    FROM   [AceTLogData].[Tlog].[tender_correct]
           AND transactiondatetime > '2022-09-21 00:00:00.000'
           AND transactiondatetime < '2022-09-22 00:03:00.000'
    GROUP  BY storenumber, bbbtendertypetext
    ORDER  BY storenumber
) s2 on s1.storenumber = s2.storenumber

Note that there is no where section in the second query

CodePudding user response：

The query works in a way but it gives me duplicate results. Will try to tweak it. bbbtendertypetext bbbtendertypetext2 NET_VALUE AmericanExpress AmericanExpress 2067.16 AmericanExpress Cash 2067.16 AmericanExpress Mastercard 2067.16 AmericanExpress MDSE Credit 2067.16

CodePudding user response：

Assuming you want to combine the SUM values of query A, and query B above (unique values GROUPED BY storenumber and bbbtendertypetext), you can use two CTE's then INNER JOIN them on storenumber to get your result set and do an overall SUM from there.

If you want a more non-aggregated result set, remove the GROUP BY and SUM's from the main query below the CTE's.

WITH a AS (SELECT storenumber,
       bbbtendertypetext,
       transactiondatetime,
       SUM(financialchangeamount) AS charge_amount,
       COUNT(transactionid) AS Transaction_Count
FROM   [AceTLogData].[Tlog].[tender]
  GROUP  BY storenumber, bbbtendertypetext, transactiondatetime),
     b AS (SELECT storenumber,
       bbbtendertypetext,
       transactiondatetime,
       SUM(financialamounttendered) AS negative_Net_Sales,
       COUNT(transactionid) AS Transaction_Count
FROM   [AceTLogData].[Tlog].[tender_correct]
   GROUP  BY storenumber, bbbtendertypetext, transactiondatetime)
SELECT a.storenumber,
       a.bbbtendertypetext,
       SUM(a.charge_amount) AS charge_amount,
       SUM(b.negative_Net_Sales) AS negative_Net_Sales ,
       SUM(a.charge_amount - b.negative_Net_Sales) AS NET_VALUE,
       SUM(a.Transaction_Count) AS Transaction_Count
FROM a INNER JOIN b ON a.storenumber = b.storenumber
WHERE  a.transactiondatetime 
  BETWEEN '2022-09-21 00:00:00.000' AND  '2022-09-22 00:03:00.000'
GROUP BY a.storenumber, a.bbbtendertypetext

See Fiddle.