[thelist] Sum'd Union Tables

Matt Warden mwarden at gmail.com
Wed Jan 4 17:27:43 CST 2006

Hash: SHA1

Mattias Thorslund wrote:
> 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.

What is the reason that the result set would need to be grouped by both
r.segment_id and s.segment_id? Yes, I did forget to qualify which the
set should be grouped by, but that was simply a typo. It needs to be
grouped by s.segment_id because that is in the projected column list.
Also, as you point out, when the tables are correctly joined with an
*outer* join (rather than my mistake suggesting an inner join),
r.segment_id will sometimes be NULL. However, as I understand it, there
is never a r tuple with no matching s tuple, so groupbing by
s.segment_id should work.

s and r are different sets to begin with. However, once they are joined,
there is only *one set*, which is the filtered cartesian product of the
two sets.

My thought is that the summation would occur over this set. It is
difficult for me to see what other set the summation would occur over.
Admittedly, though, I cannot think of a time when I"ve had to do this,
so this is in my head only.

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

One of the great things about this list is that for a given problem one
gets a variety of solutions. Even if I find a solution that I feel is
weaker than one that has already been suggested, I still consider it
useful to suggest, if only for purely academic reasons. In this case,
though, your solution will not work on MySQL versions less than 4.1. Rob
is using MS SQL Server, but that doesn't mean everyone reading this
thread (directly or via Google searches) is as well.

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

You are absolutely correct. Thanks for catching that.


- --
Matt Warden
Miami University
Oxford, OH, USA

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


More information about the thelist mailing list