Re: Screwy behavior with SUM and multiple joins to same

On Tue, 2002-08-27 at 18:20, David Link wrote:
> Screwy behavior with SUM and multiple joins to same table:

The screwiness is in your joins:

junk=# select p.prod
junk-#  , s1.units as store_1
junk-#  , s2.units as store_2
junk-#  , sAll.units as store_All
junk-# from product p
junk-#  , sales s1
junk-#  , sales s2
junk-#  , sales sAll
junk-# where p.prod = s1.prod and s1.store=1
junk-#  and p.prod = s2.prod and s2.store=2
junk-#  and p.prod = sAll.prod and sAll.store in (1,2)
junk-#  and p.prod='A' ;
 prod | store_1 | store_2 | store_all
------+---------+---------+-----------
 A    |      50 |     100 |        50
 A    |      50 |     100 |       100
(2 rows)

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "If any of you lack wisdom, let him ask of God, who
      gives to all men generously and without reproach, and
      it will be given to him."           James 1:5