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?
For starters, the target of AS should not be in quotes
SELECT name, sum(quantity), price, price * sum (quantity) AS value
Hello,
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,
inventory.quantity,
fruit.price,
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
and
SELECT * FROM mytable WHERE lname = 'Burns'
Those are back ticks not single quotes to denote a variable name.
Why do you require back ticks around a variable name?
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.