[thelist] Sum'd Union Tables

[Mattias Thorslund]

(my response below)

Rob Smith wrote:

>Ok. I'm trying to get the net result of this query:
>
>SELECT     SUM(total_sale) AS Sale, SUM(total_margin) AS Margin,
>segment_id
>
>FROM         receipt
>
>WHERE     (DATEDIFF(month, date_created, GETDATE()) = 1) AND (status <>
>'Cancelled') AND commission_ID = 280
>
>GROUP BY segment_id
>
>UNION
>
>SELECT     SUM(total_sale) AS Sale, SUM(total_margin) AS Margin,
>segment_id
>
>FROM         return_receipt
>
>WHERE     (DATEDIFF(month, date_created, GETDATE()) = 1) AND (status <>
>'Cancelled') AND (commission_id = 280)
>
>GROUP BY segment_id
>
>ORDER BY segment_id
>
> 
>
>The data returned by this query is:
>
>31018               12740.89           1
>
>-754                  -356.19             2
>
>7573                 3009.83             2
>
>-1574                -703                  3
>
>24180.38           10380.73           3
>
>-287.4               -54.2                 4
>
>23668.5             6569.78             4
>
>11498               3262.82             8
>
>2401                 472.89              9
>
> 
>
>That's fine, but what I really really would like to see is this:
>
>31018               12740.89           1
>
>6819                 2653.64             2
>
>22606.38           9677.73             3
>
>23381.1             6515.58             4
>
>11498               3262.82             8
>
>2401                 472.89              9
>
> 
>
>So in basically, the first query returns all orders. The second unioned
>query gives the returns. All I need to do is subtract the orders from
>the returns.
>
> 
>
>Can you help?
>  
>

Well, a UNION does just that: it appends the resulting rows from each
SELECT in the statement.
 
You could simply calculate the net sale in the report.

If you really want to get the database server to do the calculation, I
think you will need sub-selects. You didn't tell us what database server
you're using, but if it supports sub-selects (such as MySQL 4.1 or
higher), you could try the following:

SELECT
    Sum(u.Sale) AS Sale, Sum(u.Margin) AS Margin, u.segment_id
FROM
    (your UNION statement below) AS u
GROUP BY u.segment_id
ORDER BY u.segment_id