PostgreSQL returning wrong values from SUM CLAUSE

Question

Hello I have 3 tables one storing the customers where I am taking the ID and using it as a FKEY

The second table is storing my store_products and I am using its ID as FKEY and it has a table called price

Then the third table I am joining both those tables and it looks like this:

SELECT * FROM order_list;
 customers_cid | store_products_spid | quantity |     order_date      | price_sum
---------------+---------------------+----------+---------------------+-------
             3 |                   6 |        3 | 2021-09-03 20:22:33 |
             3 |                   5 |        1 | 2021-09-03 20:22:33 |
             3 |                   4 |        2 | 2021-09-03 20:22:33 |
             3 |                   6 |        3 | 2021-09-03 20:38:50 |
             3 |                   5 |        1 | 2021-09-03 20:38:50 |
             3 |                   4 |        2 | 2021-09-03 20:38:50 |
(6 rows)

So example I want the first row price_sum to be 4.5 basically store_products table with corresponding id is 1.5$

SELECT * FROM store_products
 spid | dealer_product_dpid | product_name  | type | description| price | quantity
------+---------------------+---------------+------+-----------------------------+-------+----------
    3 |                  16 | Baked Beans 2 | Food | Many beans |  5.97 |      203
    4 |                  16 | Baked Beans 2 | Food | Many beans |  5.97 |      203
    5 |                   5 | Baked Beans 2 | Food | Many beans |  21.9 |      333
    6 |                   3 | Baked Beans 2 | Food | Many beans |   1.5 |      333
(4 rows)

So the first column off order_list sum_price

SELECT * FROM order_list;
 customers_cid | store_products_spid | quantity |     order_date      | price_sum
---------------+---------------------+----------+---------------------+-------
             3 |                   6 |        3 | 2021-09-03 20:22:33 |

So the first column from order_list the sum_price should obviously be 4.5 because the corresponding store_products_spid and the store_products table spid is 6 and its priced at 1.5 and the quantity is 3 from the order_list table so 3 * 1.5 = 4.5

But I am having problems getting this query and this is how I am trying to do it obviously it is wrong for some reason

SELECT SUM(sp.price * ol.quantity) FROM store_products AS sp, order_list AS ol
WHERE ol.order_date = '2021-09-03 20:22:33' AND ol.store_products_spid = 6 AND ol.customers_cid = 3;

result: 106.02

As I cant understand the result should have been 4.5 and I have put every flag condition in my WHERE Clause to limit to that table as far as I am concerned.

So how can I get it to query the correct value?

This is the customers table incase it helps.

SELECT * FROM customers
 cid | first_name | last_name |      address      | zip_code | country |       email
-----+------------+-----------+-------------------+----------+---------+-------------------
   1 | Mohamed    | Mo        | 10 Downing Street | SW1A 2AA | UK      | [email protected]
   2 | John       | Doe       | Buckingham Palace | SW1A 1AA | England | [email protected]
   3 | Jane       | Earl      | 100 Street Road   | SW2A 3AA | GB      | [email protected]
(3 rows)

Ilya Dyoshin · Accepted Answer · 2021-09-04 18:42:05Z

2

In your query you don't have a join condition between store_products and order_list.

Postgresql reads your query as "SUM EVERY price FROM store_products MULTIPLYIED BY THE quantity FROM THE ROW order_list WHERE order_list.order_date = '2021-09-03 20:22:33' AND order_list.store_products_spid = 6 AND order_list.customers_cid = 3

Add one more condition store_products.spid = order_list.store_products_spid

answered Sep 4, 2021 at 18:42

Ilya Dyoshin

4,6342 gold badges23 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Belayer · Accepted Answer · 2021-09-04 19:21:55Z

This is a perfect example of why the comma separated table list in the from clause was abandoned 30 years ago (rather should have been) by SQL 92 Standard. You do not have a predicate between the tables. Therefore you get the Cartesian product between them; every row in each table combined with every row in all other tables. The WHERE then reduces this to satisfy those conditions, but that still leaves the 1 row from order_list combined with every row from products.

-- first rewrite with proper JOIN syntax

SELECT SUM(sp.price * ol.quantity) 
  FROM store_products AS sp
  join order_list     AS ol on ???    -- missing connection between tables 
WHERE ol.order_date = '2021-09-03 20:22:33' 
  AND ol.store_products_spid = 6 
  AND ol.customers_cid = 3;

Now you can see the problem. There is no predicate in the WHERE to extract and complete the join.

From table descriptions the missing join condition is ol.store_products_spid = sp.spid. So query should be:

select sum(sp.price * ol.quantity) 
  from store_products as sp
  join order_list as ol on ol.store_products_spid = sp.spid
  where ol.order_date = '2021-09-03 20:22:33' 
    and ol.store_products_spid = 6 
    and ol.customers_cid = 3;

Note: Not tested.

Collectives™ on Stack Overflow

PostgreSQL returning wrong values from SUM CLAUSE

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related