I am trying to make a query to see the total price a customer has to pay when their order is completed, but I am having issues with this error statement. How do I get the correct query?
ERROR: argument of HAVING must be type boolean, not type money
Tables involved and inserting some fake quick data:
Some of the customers:
CREATE TABLE customers (
id integer PRIMARY KEY,
first_name varchar(50),
surname varchar(50),
country varchar(200),
email varchar(100) NOT NULL
);
INSERT INTO customers
VALUES (1, 'Mohamed', 'M', 'UK', 'momu@gmail.com');
INSERT INTO customers
VALUES (2, 'Hass', 'H', 'UK', 'hass@gmail.com');
INSERT INTO customers
VALUES (9, 'Zaza', 'Z', 'UK', 'zaza@gmail.com');
Some of the products:
CREATE TABLE store_products (
id integer PRIMARY KEY,
name varchar(100),
type varchar(50),
description text,
price money,
quantity integer
);
INSERT INTO store_products
VALUES (1, 'Portable Chain Saw', 'Garden Tools', 'cant be asked for now LOREMIPSON', 21.00, 500);
INSERT INTO store_products
VALUES (3, 'Waterproof USB Plasma Lighter', 'Ignitions',
'For all you cool smokers look even cooler with the Plasma ARC lighter', 41.70, 3000);
INSERT INTO store_products
VALUES (4, 'Digital Multimeter', 'Measurement Tools', 'Work Safely with this Multimeter', 39.24, 333);
INSERT INTO store_products
VALUES (5, 'Cordless Screwdriver', 'Hand Tools', 'Get the job done just quicker', 97.20, 248);
INSERT INTO store_products
VALUES (6, 'Rustic Wooden Wall', 'Storage', 'Hang, Hang and Hang AWAY!!', 19.35, 995);
Then I combined these tables into a many-to-many relation table ordered_items:
CREATE TABLE ordered_items (
customers_id integer REFERENCES customers (id),
store_products_id integer REFERENCES store_products (id),
PRIMARY KEY (customers_id, store_products_id),
quantity integer,
order_date date
);
INSERT INTO ordered_items
VALUES (1, 4, 2, '2020, 09, 05');
UPDATE store_products
SET quantity = quantity - (SELECT quantity FROM ordered_items WHERE ordered_items.store_products_id = 4)
WHERE id = 4;
INSERT INTO ordered_items
VALUES (1, 5, 1, '2020, 09, 05');
UPDATE store_products
SET quantity = quantity - (SELECT quantity FROM ordered_items WHERE store_products_id = 5 AND customers_id = 1)
WHERE id = 5;
INSERT INTO ordered_items
VALUES (2, 1, 1, '2020, 09, 25');
UPDATE store_products
SET quantity = quantity - (SELECT quantity FROM ordered_items WHERE store_products_id = 1 AND customers_id = 2)
WHERE id = 1;
INSERT INTO ordered_items
VALUES (2, 3, 10, '2020, 09, 25');
UPDATE store_products
SET quantity = quantity - (SELECT quantity FROM ordered_items WHERE store_products_id = 3 AND customers_id = 2)
WHERE id = 3;
INSERT INTO ordered_items
VALUES (9, 6, 20, '2021, 01, 04');
UPDATE store_products
SET quantity = quantity - (SELECT quantity FROM ordered_items WHERE store_products_id = 6 AND customers_id = 9)
WHERE id = 6;
As the customer buys an item in the store at the same time, I subtracted the original quantity they had in the store. Say, if they had 100 apples and the customer bought 5, the store has 95 apples left. This bit is all so far so good. Example:
UPDATE store_products SET quantity = quantity - (SELECT quantity FROM ordered_items WHERE store_products_id = 6 AND customers_id = 9) WHERE id = 6;
However, now when I try query the final price the customer has to pay, I am getting to this error
ERROR: argument of HAVING must be type boolean, not type money
And here is my query code so far:
-- If I put it as WHERE clause I get the same
--- ERROR: argument of WHERE must be type boolean, not type money
-- this is the WHERE query
SELECT customers.first_name || ' ' || customers.surname AS customer_name,
SUM(ordered_items.quantity) AS number_of_items_bought,
SUM(store.price) AS final_price
FROM customers
JOIN ordered_items
ON ordered_items.customers_id = customers.id
JOIN store_products AS store
ON store.id = ordered_items.store_products_id
WHERE store.price * ordered_items.quantity
GROUP BY 1;
-- AND here is my HAVING Clause same problem
-- ERROR: argument of HAVING must be type boolean, not type money
SELECT customers.first_name || ' ' || customers.surname AS customer_name,
SUM(ordered_items.quantity) AS number_of_items_bought,
SUM(store.price) AS final_price
FROM customers
JOIN ordered_items
ON ordered_items.customers_id = customers.id
JOIN store_products AS store
ON store.id = ordered_items.store_products_id
GROUP BY 1
HAVING SUM(store.price) * ordered_items.quantity;
How can I fix that?
I want the results to give this:
customer_name | number_of_items_bought | final_price ---------------+------------------------+------------- Mohamed M | 3 | ú175.68 Hass H | 11 | ú438.00 Zaza Z | 20 | ú387.00 (3 rows)
And not this, which I am getting if I don’t include any HAVING or WHERE CLAUSE where I am trying to limit and have the price * quantity
-- With this query with which doesn't tell final price as PRICE * QUANTITY
SELECT customers.first_name || ' ' || customers.surname AS customer_name,
SUM(ordered_items.quantity) AS number_of_items_bought,
SUM(store.price) AS final_price
FROM customers
JOIN ordered_items
ON ordered_items.customers_id = customers.id
JOIN store_products AS store
ON store.id = ordered_items.store_products_id
GROUP BY 1;
-- Wrong result customer_name | number_of_items_bought | final_price ---------------+------------------------+------------- Mohamed M | 3 | ú136.44 Hass H | 11 | ú62.70 Zaza Z | 20 | ú19.35 (3 rows)
How should I go about and get this query; I want price * quantity to show the right:
customer_name | number_of_items_bought | final_price ---------------+------------------------+------------- Mohamed M | 3 | ú175.68 Hass H | 11 | ú438.00 Zaza Z | 20 | ú387.00 (3 rows)
Advertisement
Answer
Cast 0 to money to give zero the same type as the calculation and thus provide for a legal comparison:
having sum(store.price) * sum(ordered_items.quantity) > 0::money
See live demo.
Note also that you must use the aggregated (ie sum) value of ordered_items.quantity