GROCERY STORE: Real Price!

You are the owner of the Grocery Store. All your products are in the database, that you have created after CodeWars SQL exercises! :)

Customer often need to now how much really they pay for the products. Manufacturers make different sizes of same product so it is hard to compare prices, sometimes they make packages look big, but the weight of the product is small.

Make a SELECT query which will tell the price per kg of the product.

Weight is in grams! Round the price_per_kg to 2 decimal places.

Order results by price_per_kg ascending, then by name ascending.

Products table schema:

• id (int)
• name (string)
• price (float)
• stock (int)
• weight (float)
• producer (string)
• country (string)

Results table schema:

• name (string)
• weight (float)
• price (float)
• price_per_kg (float)

 

Solution:

SELECT 
  name, 
  weight, 
  price, 
  ROUND((price*1000/weight)::numeric,2)::float AS price_per_kg 
FROM 
  Products 
ORDER BY price_per_kg, name

If you use '::', you can change the data type.
The result of this is equivalent to 'CAST([Value] AS [Type])' .

 

Result:

name	weight	price	price_per_kg
Starfruit	500	0.02	0.04
Black Eyed Beans	180	0.03	0.17
Cucumber	400	0.14	0.35
Grapes	890	0.42	0.47
Cacao	450	0.32	0.71
Stevia	840	0.61	0.73