[thelist] Sum'd Union Tables

Mattias Thorslund mattias at thorslund.us
Wed Jan 4 19:07:51 CST 2006

Matt Warden wrote:

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

Matt, I think you're right on this one. Grouping by s.segment_id should
be sufficient, and yes, they are one set. Don't know what confused me
here. Perhaps that the 'r' columns will contain lots of Nulls, but
aggregate functions handle that.

Aside: What does SUM() return on a column that contains ONLY nulls? 0,
or NULL? Wonder if different servers behave differently. MySQL 4.1, in
my test, returns NULL in that case.

I'm thinking of your example where you wrote:

>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

If sum(r.total_sale) happens to return NULL because there were no
returns at all, the expression (sum(s.total_sale) - sum(r.total_sale))
will return NULL as well. Wrapping the suceptible parts in an IFNULL
function should take care of it:

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

Lastly, to work with the OP's data, the minus would need to be replaced
with a plus, since the amounts in the returns are already negative.
That's just a detail.

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

This proved to be helpful to myself as well ;-)

And, it's good to have a few alternatives to choose from, if performance
is an issue. It's not always possible to determine which out of a few
alternative solutions will perform better, since that depends on how the
database server optimizes. Anyway, optimization is a different topic

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

You're welcome.


More information about the thelist mailing list