Re: sum query

[if replying, please do so to the list / newsgroup only, not to me]

On 2004-12-03, "Keith Worthington" <keithw(at)narrowpathinc(dot)com> wrote:
> Hi All,
>
> I am trying to join three tables and sum the quantities.
>
> The first table contains all of the possible items. The second table
> contains orders and the third table contains the items on each order.
>
> For all items found in tbl_item I need the total quantity on open orders.
> If an item is not on any open order the quantity should be zero.
>
> Can someone please explain the best way to do this?

First, notice that what you're asking for involves a row of output for
each item in tbl_item regardless of whether it appears in the other tables
at all. This is an indication that says "try an outer join here".

So bearing that indication in mind, we work out what the other side of the
outer join should look like. This would be a simple join on the other two
tables to give the quantity in open orders. (Note that there are two ways
to do the grouping/summation in this query; either on the result of the
two-table join or on the final result.)

You used the same table name twice in your example data, I'll assume that
was an error and that the third table should have been called tbl_order_item.
Here then is how to construct the query:

Start by working out the quantities:

select oi.id,sum(oi.quantity)
from tbl_order_item oi join tbl_order o using (order_id)
where o.closed=false
group by id;
id | sum
------+-----
GH12 | 4
AB12 | 15
CD34 | 5
(3 rows)

Now outer-join that against tbl_item:

select id,quantity
from (select oi.id,sum(oi.quantity) as quantity
from tbl_order_item oi join tbl_order o using (order_id)
where o.closed=false group by id) as oj
right join tbl_item i using (id);
id | quantity
------+----------
AB12 | 15
CD34 | 5
EF34 |
GH12 | 4
JK56 |
(5 rows)

However this gives us NULL for the quantities not appearing on any open
order. Since we want zero instead, we remove the nulls with COALESCE:

select id,coalesce(quantity,0) as quantity
from (select oi.id,sum(oi.quantity) as quantity
from tbl_order_item oi join tbl_order o using (order_id)
where o.closed=false group by id) as oj
right join tbl_item i using (id);
id | quantity
------+----------
AB12 | 15
CD34 | 5
EF34 | 0
GH12 | 4
JK56 | 0
(5 rows)

And we have the desired result. Notice that I have not used ORDER BY; if
you want results in a given order, add that yourself.

I mentioned that the grouping could be done in two ways. Here is the other
way:

Start with the ungrouped quantity figures:

select oi.id,oi.quantity
from tbl_order_item oi join tbl_order o using (order_id)
where o.closed=false;
id | quantity
------+----------
AB12 | 10
CD34 | 5
GH12 | 4
AB12 | 5
(4 rows)

Outer-join against tbl_item:

select id,quantity
from (select oi.id,oi.quantity
from tbl_order_item oi join tbl_order o using (order_id)
where o.closed=false) as oj
right join tbl_item i using (id);
id | quantity
------+----------
AB12 | 10
AB12 | 5
CD34 | 5
EF34 |
GH12 | 4
JK56 |
(6 rows)

Unlike with the previous version, this time we can flatten out the inner
select (should make no difference to performance but may be more readable):

select id,quantity
from tbl_order_item oi
join tbl_order o on (o.order_id=oi.order_id and o.closed=false)
right join tbl_item i using (id);
id | quantity
------+----------
AB12 | 10
AB12 | 5
CD34 | 5
EF34 |
GH12 | 4
JK56 |
(6 rows)

And then group the values and handle nulls:

select id,coalesce(sum(quantity),0) as quantity
from tbl_order_item oi
join tbl_order o on (o.order_id=oi.order_id and o.closed=false)
right join tbl_item i using (id)
group by id;
id | quantity
------+----------
AB12 | 15
CD34 | 5
EF34 | 0
GH12 | 4
JK56 | 0
(5 rows)

Notice I haven't used either IN or UNION. Using IN in place of a join is
unwise (even though recent versions can sometimes plan it as though it were
a join); using UNION in place of an outer join is _very_ unwise. (In fact
UNION / INTERSECT / EXCEPT should normally be reserved for those cases
where there is simply no alternative.)

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services