MERGE a UNION query results down to one row per KEY value

This is a lengthy query, it polls one LIVE database for certain sales information, then a UNION runs the same exact query on the ARCHIVE database for the same information. The results show each store's sales from the two queries on two lines each, one for each DB polled.

The ideal result would merge the results down to ONE line per store, something I'm currently doing in Excel instead.

The QUERY:

DECLARE @FROM DATETIME, @TO DATETIME

    SET @FROM = '12-30-2013 03:00:00'
    SET @TO   = '12-28-2014 03:00:00'

SELECT d.DivisionName, (SUM(PaymentAmount) - SUM(TipAmount))[CC w/o Tips], SUM(TipAmount) [Tips], SUM(PaymentAmount) [Receipts]
  FROM LFLive.POS.OrderPayments  op WITH(NOLOCK)
  JOIN LFLive.POS.Orders          o WITH(NOLOCK) ON o.OrderId=op.OrderId  
  JOIN LFLive.POS.Payments        p WITH(NOLOCK) ON p.PaymentId=op.PaymentId 
  JOIN LFLive.Directory.Divisions d WITH(NOLOCK) ON d.DivisionId=o.DivisionId  
 WHERE op.IsVoided=0 AND op.IsDeleted=0 AND o.IsVoided=0 AND o.IsClosed=1
   AND o.OrderTypeId = 2 AND p.PaymentTypeId = 6 
   AND o.DateClosed BETWEEN @FROM AND @TO
 GROUP BY d.DivisionName

 UNION

SELECT d.DivisionName, (SUM(PaymentAmount) - SUM(TipAmount))[CC w/o Tips], SUM(TipAmount) [Tips], SUM(PaymentAmount) [Receipts]
  FROM LFArchive.POS.OrderPayments op WITH(NOLOCK)
  JOIN LFArchive.POS.Orders         o WITH(NOLOCK) ON o.OrderId=op.OrderId  
  JOIN LFLive.POS.Payments                 p WITH(NOLOCK) ON P.PaymentId=op.PaymentId 
  JOIN LFLive.Directory.Divisions          d WITH(NOLOCK) ON d.DivisionId=o.DivisionId  
 WHERE op.IsVoided=0 AND op.IsDeleted=0 AND o.IsVoided=0 AND o.IsClosed=1
   AND o.OrderTypeId = 2 AND p.PaymentTypeId = 6 
   AND o.DateClosed BETWEEN @FROM AND @TO
 GROUP BY d.DivisionName
 ORDER BY d.DivisionName

Notice the LFArchive in two lines of the second half? That's the only difference.

The RESULTS:

```
DivisionName CC w/o Tips Tips Receipts
------------ ----------- ---- --------

It seems you just need to use a derived table or common table expression with a new grouping level:

DECLARE 
    @FROM datetime = CONVERT(datetime, '12-30-2013 03:00:00', 101), 
    @TO datetime = CONVERT(datetime, '12-28-2014 03:00:00', 101);

WITH Combined AS
(
    SELECT 
        DivisionName = d.DivisionName, 
        [CC w/o Tips] = (SUM(PaymentAmount) - SUM(TipAmount)), 
        [Tips] = SUM(TipAmount),
        [Receipts] = SUM(PaymentAmount)
    FROM LFLive.POS.OrderPayments AS op
    JOIN LFLive.POS.Orders AS o
        ON o.OrderId=op.OrderId
    JOIN LFLive.POS.Payments AS p
        ON p.PaymentId=op.PaymentId 
    JOIN LFLive.Directory.Divisions AS d 
        ON d.DivisionId=o.DivisionId  
    WHERE 
        op.IsVoided=0 
        AND op.IsDeleted=0 
        AND o.IsVoided=0 
        AND o.IsClosed=1
        AND o.OrderTypeId = 2 
        AND p.PaymentTypeId = 6 
        AND o.DateClosed BETWEEN @FROM AND @TO
    GROUP BY 
        d.DivisionName

    UNION ALL -- Not UNION!

    SELECT 
        DivisionName = d.DivisionName, 
        [CC w/o Tips] = (SUM(PaymentAmount) - SUM(TipAmount)), 
        [Tips] = SUM(TipAmount),
        [Receipts] = SUM(PaymentAmount)
    FROM LFArchive.POS.OrderPayments AS op
    JOIN LFArchive.POS.Orders AS o
        ON o.OrderId=op.OrderId
    JOIN LFLive.POS.Payments AS p
        ON p.PaymentId=op.PaymentId 
    JOIN LFLive.Directory.Divisions AS d 
        ON d.DivisionId=o.DivisionId  
    WHERE 
        op.IsVoided=0 
        AND op.IsDeleted=0 
        AND o.IsVoided=0 
        AND o.IsClosed=1
        AND o.OrderTypeId = 2 
        AND p.PaymentTypeId = 6 
        AND o.DateClosed BETWEEN @FROM AND @TO
    GROUP BY 
        d.DivisionName
)
SELECT
    C.DivisionName,
    [CC w/o Tips] = SUM(C.[CC w/o Tips]),
    [Tips] = SUM(C.Tips),
    [Receipts] = SUM(C.Receipts)
FROM Combined AS C
GROUP BY
    C.DivisionName
ORDER BY 
    C.DivisionName;

I replaced the UNION with UNION ALL because you don't want duplicate removal.

StackExchange Database Administrators Q#89226, answer score: 4