Re: Trees in SQL
Date: 4 Jul 2002 11:41:07 -0700
Message-ID: <c0d87ec0.0207041041.3954de35_at_posting.google.com>
>> The fun part is that the sum() function yields null if any summand
is
null, so if you're summing child nodes to parents, you only get a
non-null value if all the child nodes are non-null. <<
Nope: SUM() and all other aggregate functions drop the NULLs first,
then do their math; the exception is COUNT(*), which is a set function
and not really an aggregate function.
The nice part of the nested set model is that you use a GROUP BY over
SELECT O2.emp, SUM(S1.salary)
FROM OrgChart AS O1, OrgChart AS O2,
Salaries AS S1
WHERE O1.lft BETWEEN O2.lft AND O2.rgt
AND O1.emp = S1.emp
GROUP BY O2.emp;
It sounds like you would use something like
SELECT T2.upc, SUM(price * discount) ... Received on Thu Jul 04 2002 - 20:41:07 CEST