How do carry out arithmetic functions using join in SQL?

Question

Products

productid	productname	unitprice
1	potato	5

OrderDetails

ordered	productid	unitprice	discount
1	1	5	.15

I am trying to list all products using the highest price (which would be without the discount). So I need to calculate the price without the discount and list that as unitprice alongside the productid and productname.

SELECT products.productid, products.productname, orderdetails.unitprice 
    FROM products
    INNER JOIN orderdetails ON products.productid = orderdetails.productid
    WHERE orderdetails.unitprice = 
        (SELECT (orderdetails.unitprice/( 1- orderdetails.discount)) 
        AS highest_unitprice 
        FROM orderdetails);

I get this error:

single-row subquery returns more than one row

How can I fix this?

Well, what single value do you want to compare to? Why are you calling it highest price when you don't do anything to return just the highest price (per product)? — philipxy, Oct 13 '22 at 02:14
Please either ask about 1 bad query/function with the obligatory [mre] & why you think it should return something else at the 1st subexpression that it doesn't give what you expect, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do & ideally a [mre]. But please ask about the former 1st because misconceptions in the former will get in the way of understanding the latter. And bad code doesn't tell us what you wish it would do. [ask] [Help] — philipxy, Oct 13 '22 at 02:15
Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) — philipxy, Oct 13 '22 at 02:15

score 0 · Answer 1 · answered Oct 13 '22 at 02:19

Maybe this will compile->

SELECT *
FROM
(
    SELECT products.productid, products.productname, orderdetails.unitprice,
        ROW_NUMBER()OVER(PARTITION BY orderdetails.productid ORDER BY  orderdetails.unitprice) AS PriceIndex
    FROM products
    INNER JOIN orderdetails ON products.productid = orderdetails.productid
)AS X
WHERE
    PriceIndex=1

How do carry out arithmetic functions using join in SQL?

1 Answers1