[thelist] Sum'd Union Tables

[Mattias Thorslund]

Matt Warden wrote:

> I'll take a stab in the dark here.


I don't mean to be rude, but for your benefit (and other's) I've
provided comments below. I have responded previously with a solution
that likely will work fine.

> You need to get the receipt and return receipt items on the same row so
> you can do calculations on them. For that, you need a join. Luckily your
> tables are set up to allow this easily (s for sale, r for return):
>
> select *
> from receipt s, return_receipt r
> where s.segment_id = r.segment_id
> order by segment_id


That would produce an INNER JOIN, which would require that there is a
return_receipt.segment_id for each sale.segment_id. If you study his
result set, you'll see that this is not the case. I.e. not all rows in
's' have a matching 'r'.

A LEFT OUTER JOIN would work better:

Either (1) with the MSSQL-specific syntax:
WHERE s.segment_id *= r.segment_id

or (2) with standard SQL:
FROM receipt s
  LEFT OUTER JOIN return_receipt r
  ON (s.segment_id = r.segment_id)

>
> There's our join; now let's project the results over the columns we want:
>
> select sum(s.total_sale), sum(r.total_sale),
>        sum(s.total_margin), sum(r.total_margin),
>        s.segment_id
> from receipt s, return_receipt r
> where s.segment_id = r.segment_id
> group by segment_id
> order by segment_id
>
> (Notice that we have to put in a group by clause now.)


I doubt this will work: it will likely complain that segment_id is
ambigous in the GROUP BY and ORDER BY clauses, which it is. "GROUP BY
s.segment_id, r.segment_id" won't produce the desired result either.
Also, s and r are different sets, so I doubt they can be summed up as
you suggest.

The idea of getting the sums in different columns is a useful one,
though it's not strictly needed in this case. I don't think it's
possible without sub-selects:

SELECT
    s.ReceiptSale,
    r.ReturnSale,
    (s.ReceiptSale + r.ReturnSale) as NetSale, --note: returns are
already negative
    s.ReceiptMargin,
    r.ReturnMargin,
    (s.ReceiptMargin + r.ReturnMargin) as NetReturn, --note: returns are
already negative
    s.segment_id
FROM
    (SELECT SUM(total_sale) as ReceiptSale, SUM(total_margin) as
ReceiptMargin, segment_id
     FROM receipt
    WHERE [date conditions etc...]
    GROUP BY segment_id) AS s
    LEFT OUTER JOIN
    (SELECT SUM(total_sale) as ReturnSale, SUM(total_margin) as
ReturnMargin, segment_id
    FROM return_receipt
    WHERE [date conditions etc...]
    GROUP BY segment_id) AS r
    ON s.segment_id = r.segment_id
ORDER BY
    s.segment_id --must specify one: the one at the left side of a left
join is the correct one...


This is a little more elaborate than my previous suggestion, which was
simply:

SELECT
    Sum(u.Sale) AS Sale, Sum(u.Margin) AS Margin, u.segment_id
FROM
    (OP's UNION statement) AS u
GROUP BY u.segment_id
ORDER BY u.segment_id




Have fun!

/Mattias