[thelist] Sum'd Union Tables

[Matt Warden]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hi Rob,


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
> 

I'll take a stab in the dark here.

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

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.)

Now we have all the data we want, and we could calculate from there, but
I think we can actually get the calculations directly via SQL:

select sum(s.total_sale - r.total_sale) profit,
       sum(s.total_margin - r.total_margin) profitmargin,
       s.segment_id
from receipt s, return_receipt r
where s.segment_id = r.segment_id
group by segment_id
order by segment_id

This will give us the data we want, but this formulation is probably faster:

select (sum(s.total_sale) - sum(r.total_sale)) profit,
       (sum(s.total_margin) - sum(r.total_margin)) profitmargin,
       s.segment_id
from receipt s, return_receipt r
where s.segment_id = r.segment_id
group by segment_id
order by segment_id

Give that a shot and let me know how it turns out.

Good luck,

- --
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDvDKTrI3LObhzHRMRAvm/AJ99QAgBbPtU7P0iKy3pM2opuyA+9ACeNxf1
UnAnkF208uqi9WuuRm+gmXA=
=TKrU
-----END PGP SIGNATURE-----