[thelist] Sum'd Union Tables
Matt Warden
mwarden at gmail.com
Wed Jan 4 14:39:48 CST 2006
-----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-----
More information about the thelist
mailing list