Sql-server – MERGE a UNION query results down to one row per KEY value

group bysql serversql-server-2008

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
------------    -----------     ----            ---------
Store1          96794.67        18875.97        115670.64
Store1         342488.77        67588.71        410077.48
Store2          90863.25        20353.98        111217.23
Store2         315051.32        69489.22        384540.54
Store3          82561.83        15213.77        97775.60
Store3         302811.03        54493.71        357304.74

What I'd rather have is ONE line per store… never tried to merge down a UNION results output so not sure exactly how to ask the question.

DESIRED RESULT:

DivisionName    CC w/o Tips     Tips            Receipts
------------    -----------     ----            ---------
Store1          439283.44       86464.68        525748.12
Store2          405914.57       89843.20        495757.77
Store3          385372.86       69707.48        455080.34

Best Answer

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.

Related Question