Matt Matt - 4 months ago 20
SQL Question

(x*2) > y SQL display statement

i'm struggling to get my function to display the items that are more than double the 'retail' price. My current code is;

SELECT DISTINCT C.COMPANY_NAME, S.STORE_ADDRESS,
I.ITEM_ID, I.ITEM_NAME, I.COST, I.RETAIL, I.MAKER
FROM COMPANY C, INVENTORY I, STORE S
WHERE(
I.COST > I.RETAIL * 2
GROUP BY ITEM_ID
);


My variables are all numbers. I'm getting the missing right paranthesis error on the line with I.COST > I.RETAIL * 2.

Answer

You don't have aggregation function so you don't need group by

SELECT DISTINCT C.COMPANY_NAME, S.STORE_ADDRESS,
I.ITEM_ID, I.ITEM_NAME, I.COST, I.RETAIL, I.MAKER
FROM COMPANY C, INVENTORY I, STORE S
WHERE  I.COST > (I.RETAIL * 2);

be careful that you have not relation condition between the tables