According to my professor's program, it says that the snippet of code that i wrote has a syntax or runtime error.

This is my code:

#S3 display fruit name, inventory quantity sum, price and sum quantity*price displayed as value for each row in fruit
SELECT name, sum(quantity), price, price * sum (quantity) AS "value"
FROM inventory
RIGHT JOIN fruit ON inventory.fruitID = fruit.fruitID
GROUP BY fruit.fruitID;

What is wrong with it?

4 Years
Discussion Span
Last Post by Reverend Jim

For starters, the target of AS should not be in quotes

SELECT name, sum(quantity), price, price * sum (quantity) AS value


The quote mark around value is the wrong type of qoute. The instructions say for every entry in the fruit table which means that you should run the query from the fruit table and lookup data from the inventory table. And since you are using a join it is normally a good idea to include the table names as part of the field name.

SELECT fruit.name, 
fruit.price * inventory.quantity AS `Value`
FROM fruit
LEFT JOIN fruit ON inventory.fruitID = fruit.fruitID
ORDER BY fruit.name;

You do NOT want single quotes around Value. Single quotes are for specifying literals. The identifier after the AS is the name you want to give the field returning the requested number. In this case, the column will have the name Value. There is a difference between

SELECT lname AS LastName FROM mytable


SELECT * FROM mytable WHERE lname = 'Burns'
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.